0

new to Stack overflow, bear with me. I am an intermediate VBA coder, based on Excel. Have run into an issue, and am not sure if it is me, VBA and/or if there is a work around.

I have a single Workbook, multiple Worksheets. Think of it as a data repository; I access it basically in the background from another macro-enabled Workbook. From this workbook, I need to get data from at least two of the worksheets. First thing I need to do is count the number of rows across two of the worksheets, to determine how much data a future routine will need to account for. My issue is, the first time I ask to get data from Sheet1, it works exactly as I would expect. But after changing to look at Sheet3 in the very next statement, I do NOT get the right answer, in fact I get what appears to be info from the Sheet1 worksheet, not the Sheet3 worksheet.

Code snippets to show my syntax: 

    ' Open the source excel workbook in read only mode

    Set src = workbooks.open(filesource info, True, True)

The above works just fine, filesource info is a long directory and filename string; it is accurate.

    sheet1Rows = src.Worksheets("sheet1").Range("B3:B" & Cells(Rows.Count, "B").End(xlUp).row).Rows.Count

The above code WORKS EXACTLY as I expect (returns 264 in my current app), but the NEXT line of code reads:

    Sheet3Rows = src.Worksheets("sheet3").Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).row).Rows.Count

The above code returns the WRONG answer (265, but should be 23!). In fact, as shown here, it returns one more than Sheet1Rows, which I attribute to two facts. 1) VBA is still looking at Sheet1, not Sheet3 and 2) the code is reading rows starting at range A2, not range B3.

So, what's the secret to open one workbook, but then reference multiple worksheets while the workbook is open for use?

braX
  • 11,506
  • 5
  • 20
  • 33
WTC_CA
  • 1
  • 1

1 Answers1

0

Cells() without a worksheet qualifier defaults to the activesheet, which may not be what you expect

sheet1Rows = src.Worksheets("sheet1").Range( _
      "B3:B" & Cells(Rows.Count, "B").End(xlUp).row).Rows.Count

would be better as:

With src.Worksheets("sheet1")
    sheet1Rows = .Range("B3:B" & .Cells(.Rows.Count, "B").End(xlUp).row).Rows.Count
End With

What is the default scope of worksheets and cells and range?

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Thanks - I ended up solving this an entirely different way - now using ADO to get access to the second spreadsheet. Works far better for my application, as it turns out. Really appreciate your response and will try this method when applicable. – WTC_CA Dec 05 '19 at 23:20