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

[Intermedius]

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

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

 

[cooper645]

Just a thought - Has anyone changed the name from Sheet1 to something different?

 

[Intermedius]

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]

What about changing

Workbooks("Forecast").Worksheets("Sheet1").Activate

to

Workbooks("Forecast.xlsx").Worksheets("Sheet1").Activate

 

[Intermedius]

What about changing

Workbooks("Forecast").Worksheets("Sheet1").Activate

to

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

'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]

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]

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]

@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]

@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.