VBA Copy Worksheets
 

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.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

VBA Copy Worksheets
 

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 _
befo=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


-----Original Message-----

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.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

.

VBA Copy Worksheets
 

Hi! Patrick Molloy

Thank you for the help.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/