S
schaapiee
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 Before:=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
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 Before:=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