Exporting All Sheets to New Workbook

[bearcub]

I was trying to export all sheets from one file today to separate workbooks. Reason is that I had to create a new file for each sheet in this file so I could import each workbook separately into a commissions application. Each sheet was a renewal schedule for a difficult regional vice president. I had to create about 10 files because I had 10 separate sheets in the main file. I didn't want to move each sheet, just copy each sheet into an individual workbook.

I looked around on the web for some vb code but couldn't find the exact code that I needed. I stumbled on this code I'm posting now which I would like to tweak. I would like to make it more dynamic and robust.

I remember there are forum rules regarding posting code but it has been a couple of years since I've posted a question on this forum. I apologize in the advance for not remembering the protocol.

Right now, the code is telling me where I can save the files but I would like to modify the code so that I can point to a location of my choice. Plus, I would like to make the replace the "Master Sheet" name with a generic name because I might have different sheet names I don't want to export.

Would this be difficult to do? Or, is their a more efficient code that is more dynamic?

Thank you for your help,

Michael

[/B]

[TABLE]
[TR]
[TD]Sub Export All Sheet to Individual Workbooks ()[/TD]
[/TR]
[TR]
[TD]Dim a as Integer[/TD]
[/TR]
[TR]
[TD]Dim ws as Worksheet[/TD]
[/TR]
[TR]
[TD]Dim wb as workbook[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]a = ThisWorkbook.Worksheets.Count[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]For I = 1 to a[/TD]
[/TR]
[TR]
[TD]If ThisWorkbook.Worksheets(i) . Name <>"Master Sheet" Then  <-- Change Master sheet to sheet name  of my choice[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Set wb = Workbooks.Add[/TD]
[/TR]
[TR]
[TD]This Workbook. Worksheets(i). Copy before: wb.Worksheets (1)[/TD]
[/TR]
[TR]
[TD]wb.SaveAs "C:\Users\ExcelDestination\Desktop\All Files" & "\" & ActiveSheet.Name & ".xlsx"  <--I would like to replace this with the ability to point to where I want to save the file[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]wb.Close savechanges = True[/TD]
[/TR]
[TR]
[TD]End if[/TD]
[/TR]
[TR]
[TD]Next i[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]ThisWorkbook. Activate[/TD]
[/TR]
[TR]
[TD]ThisWorkbook.Worksheets("Master Sheet").Activate[/TD]
[/TR]
[TR]
[TD]ThisWorkbook.Worksheets("Master Sheet").Cells (1,1).Select[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]MsgBox ("Task Completed")[/TD]
[/TR]
[TR]
[TD]End Sub[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[/TABLE]
[B][End Code][/B]

 

[jolivanes]

Thanks for the update.
I don't use cloud storage but you somehow should be able to make it work.
I would say that there must be examples around on how to make it work.
Good Luck