Subscript out of range error

gr8giz

New Member
Joined
Mar 12, 2007
Messages
23
Hi,

I am using Excel 2003 on Win XP Pro.

In my code I defined a workbook object named Newbook and then used following code to assign it a workbook

Set Newbook = Workbooks.Add

When I try to copy from another open workbook to this Newbook, I get error that Subscript is out of range.

Here is the code I used:

Workbooks("EVDO Daily_Trending_PHIL1").Worksheets("Fwd_MB").Range("A:IV").Copy Workbooks("Newbook").Worksheets("Fwd_MB").Range("A:IV")

Any help is appreciated. Thanks,
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Newbook is not the name of the sheet, it is the name of the Object variable, by default it will probably be something like Book1

You may also need to use .xls, but you can either reference it as the ActiveWorkbook, get its name after it is created, or save it out right there as something and refer to that name:

You also have to have the name of the worksheet in the new workbook:

This seems to work:

Code:
Sub test()
Dim newbook As Workbook, t As String
Set newbook = Workbooks.Add
t = ActiveWorkbook.Name
Workbooks(t).Sheets("Sheet1").Name = "Fwd_MB"
Workbooks("EVDO Daily_Trending_PHIL1.xls").Worksheets("Fwd_MB").Range("A:IV").Copy Workbooks(t).Worksheets("Fwd_MB").Range("A1")
End Sub
 
Upvote 0
...

so you probably need something like


Code:
Workbooks("EVDO Daily_Trending_PHIL1").Worksheets("Fwd_MB").Range("A:IV").Copy Destination:=NewBook.Worksheets("Fwd_MB").Range("A1")
 
Upvote 0
Thanks I was referring to the variable wrongly I guess. Instead of Workbooks("Newbook"), just Newbook works.

I had problem referring to objects defined as variable. This will help me.
 
Upvote 0
VOG II:

How you can refer to a sheet that doesn't exist with that name yet?

gr8giz:
If you're only adding a workbook once, you could dispense with the object variable entirely and just say Workbooks.Add

Code:
Sub test()
Dim t As String
Workbooks.Add
t = ActiveWorkbook.Name
Workbooks(t).Sheets("Sheet1").Name = "Fwd_MB"
Workbooks("EVDO Daily_Trending_PHIL1.xls").Worksheets("Fwd_MB").Range("A:IV").Copy Workbooks(t).Worksheets("Fwd_MB").Range("A1")
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top