0

beginner VBA coder here, and running into an error message I just can't seem to solve.

When I step through the code (F8), the last row below flows through to my errorhandler, instead of the code immediately after it, leading me to believe that the error in the 'LastRowYTD' row:

Sub EMEA_Dashboard_VBA()

Dim LastRowYTD As Long
Dim MEF_YTD As Workbook

On Error GoTo ErrorOutputYTD

LastRowYTD = Worksheets("Dashboard").Range("A" & Rows.Count).End(xlUp).Row - 2

I had the code working before, but for some reason, it doesn't want to cooperate now.

I would be very appreciative of any suggestions! Thank you :)

Learning...
  • 1
  • 1
  • 1
    if you take out the error handling, what is the error that happens at that line? – Scott Craner Aug 01 '22 at 18:20
  • 1
    `Worksheets("Dashboard")` without an explicit workbook qualifier like `ActiveWorkbook` or `ThisWorkbook` will likely be prone to random bugs if you work with multiple workbooks. – Tim Williams Aug 01 '22 at 18:30
  • When I delete the error handler, the following error notice appears: Run-time error '9': Subscript out of range. When I hit debug, it highlights the last row that I referenced above – Learning... Aug 01 '22 at 18:37
  • 2
    Then the Active Workbook does not have a worksheet with the name "Dashboard". As @TimWilliams stated maybe using `LastRowYTD = ThisWorkbook.Worksheets("Dashboard").Range("A" & Rows.Count).End(xlUp).Row - 2` will fix it if you have multiple workbooks open. If you only have the one workbook open then make sure there are not spaces in the worksheet name that you are missing in your code. – Scott Craner Aug 01 '22 at 18:39
  • Thanks so much Tim and Scott. Changing the LastRowYTD to what Scott proposed is working, as F8 is allowing me to step through to the next line. However, it looks like the next two lines (not previously shared above) are the last hitch `Set MEF_YTD = Workbooks.Open(Worksheets("Dashboard").Range("F39").Value)` and `Set myRangeYTD = ActiveWorkbook.Worksheets("Summary").Range("A915:AJ1033")`. I imagine it relates to the same issue (same error notice), in not having a qualifier. Is anything in those two lines jumping out at you as error-prone? Again, thank you. – Learning... Aug 02 '22 at 09:40
  • `Set MEF_YTD = Workbooks.Open(ThisWorkbook.Worksheets("Dashboard").Range("F39").Value)` Then `Set myRangeYTD = MEF_YTD.Worksheets("Summary").Range("A915:AJ1033")` assuming the second line refers to the workbook opened in the first line. – Tim Williams Aug 02 '22 at 15:48

0 Answers0