0

I am using a union subroutine to grab data from one sheet, parse it, and place it in another. Why does this code not work when it is called, but works when I step through it?

Sub Get_Time_ps()

Application.ScreenUpdating = False

Dim Row As Long
Dim LastRow As Long
Dim i As Long
Dim Rng As Range

Set Rng = Range("A9")

i = 15
ActiveWorkbook.Sheets(3).Activate
Range("A9").Select
Row = Selection.End(xlDown).Row
LastRow = Row - 8
Range("A9").Select
For i = 15 To LastRow Step 6
    Set Rng = Union(Rng, Range("A" & i))
Next i
Row = Rng.Count - 1
Rng.Select
Selection.Copy
Sheets("Raw Data").Select
Range("A23").Select
ActiveSheet.Paste

Cells.Find("Time").Select
Selection.Copy
Cells.Find("Time").Offset(1, 0).Select
Range(Selection, Selection.Offset(Row, 4)).Select
Selection.PasteSpecial xlFormats


Application.ScreenUpdating = True

End Sub
halfer
  • 19,824
  • 17
  • 99
  • 186
Aaron Brockmeyer
  • 21
  • 3
  • 1
    You need to have explicit references to the sheet (and workbook if you are using multiple) with range references. It can't unionize ranges from multiple sheets. – Warcupine May 05 '21 at 14:57
  • 1
    And don't forget to check this post: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba Has the way links are created changed? – Darren Bartrup-Cook May 05 '21 at 15:02

1 Answers1

0

Code that works one way in the debugger, and then blows up otherwise, 99% of the time means you've got implicit ActiveSheet references, and code that is making assumptions about what worksheet is currently active.

Rng is assigned to Range("A9") so it's off whatever worksheet is active at that time - but then two lines later we Activate another sheet (maybe!), and then...

Set Rng = Union(Rng, Range("A" & i))

If ActiveWorkbook.Sheets(3).Activate changed what sheet is currently active, then this Union member call will blow up every time, because you can't Union two Range objects that belong on different worksheets.

Rubberduck can help you easily find all implicit references to the ActiveSheet in your project: consider qualifying them all with a proper Worksheet object, be explicit about what your intent is, and then as you use Worksheet objects instead of implicitly qualifying worksheet member calls with whatever the active sheet is, you'll find that you no longer need to Select and Activate anything anywhere, and that your code is much more robust and reliable for it.

Removing the ActiveWorkbook.Sheets(3).Activate instruction, or moving it before Rng is assigned, should "fix" the immediate problem at hand.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235