CODEWITHSUNDEEP
excelvba
Feldy
Feldy

Reputation: 11

excel vba set workbook w/ path, reference worksheets within - run time error 9er

newbie trying this for the first time... i'm getting errors, mostly "subscript out of range". Currently issue highlights -> Set ICE line

Goal of this, I'd like to learn how to set workbooks w/ a path, and reference worksheets within. Then ultimately clear existing data and replace it with renewed data from the other workbook. wouldn't be surprised if more errors exist... Really appreciate your help!

Dim ICE As Workbook
Dim RTL As Workbook
Dim IDS As Workbook
Dim ICEFile As String
    ICEFile = "C:\Users\m\Documents\Rental Projects\Ice Report\ICE Report.xlsx"
Dim RTLFile As String
    RTLFile = "C:\Users\m\Documents\Rental Projects\Ice Report\OpenRentalAgreements.xlsx"
Dim IDSFile As String
    IDSFile = "C:\Users\m\Documents\Rental Projects\Ice Report\ActiveIDsByManagers.xlsx"
Set ICE = Workbooks(ICEFile)
Set RTL = Workbooks(RTLFile)
Set IDS = Workbooks(IDSFile)
Dim ICERR As Worksheet: Set ICERR = ICE.Sheets("RRDataINPUT")
Dim ICEID As Worksheet: Set ICEID = ICE.Sheets("IDsReconINPUT")
       
ICE.Activate
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
ICERR.Range("AH:BU").Select
Selection.Clear
ICEID.Range("M:AK").Select
Selection.Clear
ICERR.Range("AH:BU").Value = IDS.Range("B:Z").Value
ICEID.Range("M:AK").Value = RTL.Range("A:AN").Value
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Upvotes: 0

Views: 49

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

Please, firstly replace

Set ICE = Workbooks(ICEFile)

with

Set ICE = Workbooks.Open(ICEFile)

Only an open Workbook can be referenced. If they are already open, their name must be used, not their full name. This can be obtained in many ways. I prefer the next one:

     ICEFile = Split(ICEFile, "\")(UBound(Split(ICEFile, "\")))

Do the same with the next two code lines...

When you debug, it is not appropriate to use Application.ScreenUpdating = False. You need to see what is happening, running the code line by line (pressing F8 in VBE...).

ICERR.Range("AH:BU").Value = IDS.Range("B:Z").Value

will not work. Both ranges must have the same number of rows (they have) and columns...

Try Debug.Print Range("AH:BU").Columns.count, Range("B:Z").Columns.count and see what it returns in Immediate Window (Ctrl + G in IDE).

The same will happen for

ICEID.Range("M:AK").Value = RTL.Range("A:AN").Value

because of the same reason.

Try Range("M:AK").Columns.count, Range("A:AN").Columns.count and see what it returns in Immediate Window (Ctrl + G in IDE).

Finally, it is not necessary to select/activate anything in order to perform such actions...

Upvotes: 2

Related Questions