Creating duplicate workbooks in VB

[jpsimmon]

Hi Guys,
I am using a code to create duplicate workbooks for each page of my master workbook, and send them into a folder. Is there any way I can alter this code to just pick specific worksheets to export to individual workbooks?

Sub CreateWorkbooks()
Dim wbDest As Workbook
Dim wbSource As Workbook
Dim sht As Object
Dim strSavePath As String
Dim r As Long, c As Long, ws As Worksheet
On Error GoTo ErrorHandler

Application.ScreenUpdating = False


strSavePath = "S:\Folder Pathway\Path\Folder Path\"


Set wbSource = ActiveWorkbook


For Each sht In wbSource.Sheets
r = sht.Rows.Find("*", , , , xlByRows, xlPrevious).Row
c = sht.Columns.Find("*", , , , xlByColumns, xlPrevious).Column
sht.Copy
Set ws = ActiveSheet
ws.Range("A1").Resize(r, c).Value = sht.Range("A1").Resize(r, c).Value
Set wbDest = ActiveWorkbook
wbDest.SaveAs strSavePath & sht.Name
wbDest.Close
Next

Application.ScreenUpdating = True

ErrorHandler:

End Sub

Thanks a lot!
jp

 

[Ken Cowen]

How do you want to decide what sheets to save? Do you just want a prompt for each sheet, or do you want to pre-select from a list on a form or a list on a worksheet somewhere?

Ken

 

[jpsimmon]

Thanks Ken,
I want to list what sheets to export in the macro. There is about 4 sheets in the workbook I want to export as separate xls'.

JP