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