Why is the givingType Dim coming up as blank, even though Range("B3").Value populates as &-CodePudding

Sub PullPriorYearActualsByMonth()
'
' Pull prior year actuals by month for a given budget category
'
Workbooks.Open ("S:\Finance\Budget & Forecast\2023\2023 Budget\Consolidated\Finance Use Only\Updating 2022 Budget Macro File.xlsm")

Dim i As Integer
Dim VarCellValue As String
Dim VarCellValue2 As String
Dim VarCellValue3 As String
Dim currwbk As Workbook
Dim givingType As String
Dim fileName As String


fileName = Application.InputBox("Please copy and paste the Other Revenue Filename here")

Application.EnableEvents = False
Application.DisplayAlerts = False

'Workbooks.Open ("I:\Calendar 2023\2023 Budget\2023 Working Budget\3. Other Revenue\[Enter File Name Here].xlsm")
Workbooks.Open ("I:\Calendar 2023\2023 Budget\2023 Working Budget\3. Other Revenue\" & fileName & ".xlsx")
'Workbooks.Open ("I:\Calendar 2023\2023 Budget\2023 Working Budget\3. Other Revenue\2023 Budget - Workplace Giving (3-year Average).xlsx")
Sheets(1).Activate
givingType = Range("B3").Value

See directly above. It fills in the correct value, but the dimension is empty. Do I need to do something with the "Set" function?

CodePudding user response：

Avoid using Activate, and fully qualify the Range with its parent Workbook/Worksheet.

Dim wb As Workbook
Set wb = Workbooks.Open("I:\Calendar 2023\2023 Budget\2023 Working Budget\3. Other Revenue\" & fileName & ".xlsx")

givingType = wb.Worksheets(1).Range("B3").Value