Copy a collection into a new workbook.
 

I have selected about 20 sheets, and cannot figure out how to move
them into my new workbook

Dim colWks As New Collection
Dim wkSht As Worksheet
Dim i As Long
With Workbooks("P017_procDepByOfficer.xls")
.Activate
For Each wkSht In .Worksheets
If wkSht.Name < "Documentation" Then colWks.Add wkSht
Next wkSht
If colWks.Count 0 Then
colWks(1).Select
For i = 2 To colWks.Count
colWks(i).Select False
Next i
End If
End With

How do I reference the above collection of 20 sheets and paste into a
new workbook; here is what I am working with that is erroring out on
me.

Dim WB1 As Workbook
Dim WB2 As Workbook
Dim strSavePath As String

Set WB1 = Workbooks("P017_procDepByOfficer.xls")
Set WB2 = Workbooks.Add

Sheets(colWks).Copy Befo=WB2.Sheets(1) '--this line is what I
am trying to reference!
Columns("A:I").Select
Columns("A:I").EntireColumn.AutoFit

Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sample",
Me.EOM)).Select
Sheets("Sheet3").Activate
ActiveWindow.SelectedSheets.Delete

Thanks for your help, and let me know if you have questions.
Devon

Copy a collection into a new workbook.
 

On Feb 9, 1:18 pm, "schaapiee" wrote:
I have selected about 20 sheets, and cannot figure out how to move
them into my new workbook

Dim colWks As New Collection
Dim wkSht As Worksheet
Dim i As Long
With Workbooks("P017_procDepByOfficer.xls")
.Activate
For Each wkSht In .Worksheets
If wkSht.Name < "Documentation" Then colWks.Add wkSht
Next wkSht
If colWks.Count 0 Then
colWks(1).Select
For i = 2 To colWks.Count
colWks(i).Select False
Next i
End If
End With

How do I reference the above collection of 20 sheets and paste into a
new workbook; here is what I am working with that is erroring out on
me.

Dim WB1 As Workbook
Dim WB2 As Workbook
Dim strSavePath As String

Set WB1 = Workbooks("P017_procDepByOfficer.xls")
Set WB2 = Workbooks.Add

Sheets(colWks).Copy Befo=WB2.Sheets(1) '--this line is what I
am trying to reference!
Columns("A:I").Select
Columns("A:I").EntireColumn.AutoFit

Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sample",
Me.EOM)).Select
Sheets("Sheet3").Activate
ActiveWindow.SelectedSheets.Delete

Thanks for your help, and let me know if you have questions.
Devon

Forget it, I just used sheets.copy and it worked..dont use a
collection to select worksheets.