VBA Copy Worksheets

[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.

 

[Patrick Molloy]

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

 

[Michael168]

Hi! Patrick Molloy

Thank you for the help.