Annoying Error: run-time error '9': subscript out of range

Intermedius

New Member
Joined
Oct 12, 2012
Messages
32
Hello,

I have a subroutine that requires the opening of a workbook.xlsx, copy the data from the 1st sheet and paste it into the document that is running the code.

The run time error is hanging up at the .Activate line. I've run this code on 100's of files, multiple times a month to actualize varying projects and never had this issue until today.

I've google this runtime error and all issues seem to revolve around .activate all the way back to 2008.

How is this affecting My code now? I've been running these lines for 18 months in several documents all accessing the same file at this address C:\SAP\SAP GUI\Forecast.XLSX


VBA Code:
Sub CopyPasteLatestDataFile()

Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = True

'Open and Activate Forecast Workbook and Data Sheet.

Workbooks.Open filename:="C:\SAP\SAP GUI\Forecast.XLSX"
    Workbooks("Forecast").Worksheets("Sheet1").Activate
        ActiveSheet.Range("A1").Select
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

cooper645

Well-known Member
Joined
Nov 16, 2013
Messages
622
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
Just a thought - Has anyone changed the name from Sheet1 to something different?
 

Intermedius

New Member
Joined
Oct 12, 2012
Messages
32
Just a thought - Has anyone changed the name from Sheet1 to something different?
Good question, but no. The line that precedes opens the workbook without issue, but hangs up the operation until i go into debug mode to view the error, which then the sheet pops up on My screen.

I don't get it. I'm also short on time, and will need to figure out an alternative method to activate that sheet.
 

cooper645

Well-known Member
Joined
Nov 16, 2013
Messages
622
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
What about changing
VBA Code:
Workbooks("Forecast").Worksheets("Sheet1").Activate

to

VBA Code:
Workbooks("Forecast.xlsx").Worksheets("Sheet1").Activate
 

Intermedius

New Member
Joined
Oct 12, 2012
Messages
32

ADVERTISEMENT

What about changing
VBA Code:
Workbooks("Forecast").Worksheets("Sheet1").Activate

to

VBA Code:
Workbooks("Forecast.xlsx").Worksheets("Sheet1").Activate
Man, it got Me past that stage and brought Me to the next subroutine and produced the same error.

Something must have changed at a higher level. I'm just not sure what that is. Nothing in the code, because i maintain that.

About 50 different workbooks were all updated in October, and are independent to the other, with the only synergy being they pull data from that file.

I've never even seen this runtime error before. It's now hanging up at the 1st line here. Never had 1 issue with changing the text format to this data table. lol

VBA Code:
'Convert Specified Columns to Number format

ActiveSheet.ListObjects("Forecast_Table").ListColumns(2).Range = ActiveSheet.ListObjects("Forecast_Table").ListColumns(2).Range.Value
ActiveSheet.ListObjects("Forecast_Table").ListColumns(4).Range = ActiveSheet.ListObjects("Forecast_Table").ListColumns(4).Range.Value
ActiveSheet.ListObjects("Forecast_Table").ListColumns(6).Range = ActiveSheet.ListObjects("Forecast_Table").ListColumns(6).Range.Value
ActiveSheet.ListObjects("Forecast_Table").ListColumns(7).Range = ActiveSheet.ListObjects("Forecast_Table").ListColumns(7).Range.Value
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,065
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Re the workbook error: it depends if you have show file extensions turned on in Windows, if you have them set to show then you need the extension in the code.

What error number are you getting for the ListObjects lines.
 

Intermedius

New Member
Joined
Oct 12, 2012
Messages
32

ADVERTISEMENT

Re the workbook error: it depends if you have show file extensions turned on in Windows, if you have them set to show then you need the extension in the code.

What error number are you getting for the ListObjects lines.
Genius... I changed this folder feature two weeks ago. You're the man with that. I saw several threads around the web, including here and not one mentioned this.

cooper645, thanks for helping ME dig deeper and realize the issue being greater than the code.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
@Intermedius , in general it is recommended to use the Activate and Select methods as little as possible.
Regarding your issue I am thinking of the following scenario. If the workbook is saved to disk with more than one sheets selected and the sheet that should be activated trough your code is NOT part of this selection, the Activate method errors.
 

Intermedius

New Member
Joined
Oct 12, 2012
Messages
32
@Intermedius , in general it is recommended to use the Activate and Select methods as little as possible.
Regarding your issue I am thinking of the following scenario. If the workbook is saved to disk with more than one sheets selected and the sheet that should be activated trough your code is NOT part of this selection, the Activate method errors.
I will take this comment for Active and Select into consideration. I'm not the most sophisticated with the code I write, so that was the path i chose at the time.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,163
Messages
5,640,492
Members
417,148
Latest member
pe3087te

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