Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying cells between sheets in a workbook | Excel Discussion (Misc queries) | |||
Copying multiple sheets to new Workbook | Excel Discussion (Misc queries) | |||
Sporadic Error Copying Sheets from one workbook to another | Excel Discussion (Misc queries) | |||
COPYING Workbook and sheets automatically | Excel Discussion (Misc queries) | |||
Excel workbook copying between sheets | Excel Discussion (Misc queries) |