Copying Sheets to New Workbook
Greetings,
I wrote the following code which is in a sheet, and am having trouble
figuring out what is going wrong. Basically I have a workbook that has a
number of sheets say Name1, Name2, Bill1, Sam1, Sam2, Sam3. At the click of
a button, I want to generate a new workbook and then copy the desired sheets
into the new workbook. The names Sam1, Sam2, Sam3, are in the range
"Stream_Summaries". Everything works until I get to the marked line where I
start to copy Sam1. Then I get a SUBSCRIPT OUT OF RANGE error.
This code worked beautifully when there was only Sam1 and I recently had to
add the For each... and that is when the fun started.
Can someone tell me what is going on since I am lost?
Thanks in advance for your help!
Ray
Private Sub CommandButton3_Click()
' this code builds a new workbook
Application.ScreenUpdating = False ' turn off screen updating
Dim NWB As Workbook, EWB As Workbook, NewName As String
Dim SRN As String, Block As Range
Set EWB = ActiveWorkbook ' store the active workbook name
NewName = ""
NewName = InputBox("Please enter the name for the new workbook without
the .xls ")
Set NWB = Workbooks.Add ' add new workbook
' copy the sheets
EWB.Sheets("Name1").Copy NWB.Sheets(1)
EWB.Sheets("Name2").Copy NWB.Sheets(1)
For Each Block In EWB.Worksheets("List Data").Range("Stream_Summaries")
'loop thru summaries
SRN = Block.Value ' store summary sheet name
EWB.Sheets(SRN).Copy NWB.Sheets(1) << line with error
......... more code
|