VBA Copy Worksheets

  • Thread starter Thread starter Michael168
  • Start date Start date
M

Michael168

How to copy multiple worksheets starting with worksheet's initial name
"Out1" to "Out60" to a new workbook with a new name "Out.xls" in the
vba module in one go. I want to copy the values only from old workbook
to the new workbook. The name of the old workbook is "Inventory.xls".

Thank you for the instructions.
 
Sub CopySheets()
Dim wb As Workbook
Dim ws As Worksheet
Dim arr() As String
Dim index As Long

' first get the sheets to be copied
For Each ws In ThisWorkbook.Worksheets
If ws.Name Like "OUT*" Then
index = index + 1
ReDim Preserve arr(1 To index)
arr(index) = ws.Name
End If
Next
' create a new workbook
Set wb = Workbooks.Add(xlWBATWorksheet)
ThisWorkbook.Sheets(arr).Copy _
before:=Workbooks(wb.Name).Sheets(1)

wb.SaveAs "C:\Mybook.xls"
wb.Close False

End Sub

Note you'll need to change the 'saveas' line for the
correct path & name

Patrick Molloy
Microsoft Excel MVP
 
Back
Top