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,
 

Some videos you may like

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.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
...

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")
 

gr8giz

New Member
Joined
Mar 12, 2007
Messages
23
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.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,632
Messages
5,626,000
Members
416,150
Latest member
Dawzzy

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
Top