Export/Move sheets to new book

Question

I have a workbook with many sheets. I created many checkboxes on userform that if I want to create a copy of sheets, just check to that checkboxes and click to button1, the name of these copies like: "(Excel)". How do I move those sheets "(Excel)" to a new book?

'Create sheets
Dim Ctl As Control
For Each Ctl In Me.Controls
  If Ctl.Value = True Then Run Ctl.Tag
Next

'Move Sheets name *(Excel) to other workbook. But it's only move one sheet
Dim bReplace As Boolean, sh As Worksheet
Dim bk As Workbook
bReplace = True
For Each sh In Worksheets
If sh.Name Like "*(Excel)" Then
sh.Select Replace:=bDontReplace
bReplace = False
End If
Next

Besides, I want to export those sheets to PDF by another button.

jsanchezs jsanchezs · Accepted Answer · 2016-07-26T14:53:41

Here's a simple approach to move many sheets :

Sub CopyWorkbook()

Dim currentSheet as Worksheet
Dim sheetIndex as Integer
sheetIndex = 1

For Each currentSheet in Worksheets

    Windows("source workbook").Activate 
    currentSheet.Select
    if currentSheet.Name like "*(Excel)" then
       currentSheet.Move Before:=Workbooks("target workbook").Sheets(sheetIndex) 
    End if

    sheetIndex = sheetIndex + 1

Next currentSheet

End Sub

To export them as pdf this would do inside a button click event:

   SourceWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
   Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
      "C:\Users\you\Desktop\exported_sheet.pdf", Quality:=xlQualityStandard, _
      IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
      True

Hope it helps !

Export/Move sheets to new book

1 Answers