Copying Sheets to New Workbook
 

subscript out of range would indicate that

SRN is not a valid sheet name for a sheet in EWB.

You going to have to check the names. Make sure you don't have extra spaces
or something.

--
Regards,
Tom Ogilvy



"Ray Batig" wrote in message
k.net...
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