How to make selection move through variable data in VBA

Question

I am trying to continue the process until the end of the cells, but the selection needs to change in a set order. Here's the code:

' C_P Macro
' copies from worksheet & pastes in new sheet
' Keyboard Shortcut: Ctrl+Shift+L

  Dim rng1 As Range
  Dim Look As String
  Dim iRow As Integer
  With Windows("Audubon-Crest-Rent-Roll-201502-1.xlsx").Activate
  Set rng1 = Range("A:A")
  ActiveCell = Range("A228")
  Do
    Windows("Audubon-Crest-Rent-Roll-201502-1.xlsx").Activate
    Range("A228:A239").Select
        Selection.Copy
    Windows("Audubon Crest Rent Roll 20150219-altered.xlsm").Activate
    Range("B17").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Windows("Audubon-Crest-Rent-Roll-201502-1.xlsx").Activate
    Application.CutCopyMode = False

    Range("A228:A239").Offset(13, 0).Select
    Selection.Copy
    Windows("Audubon Crest Rent Roll 20150219-altered.xlsm").Activate
    Range("B17").Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Range("B18").Select
    Loop Until IsEmpty(Cells(iRow, 1))
End With
End Sub

As you can see, what it basically does is Make a selection of 11 rows and then transpose copies the data into the next workbook at the next row. I F8'd through the procedure and I can tell that it works well for one selection then moves down 13 rows and does the next selection, BUT I'm not sure how to make it continuously move through the data. I need for it to always select 11 rows, do the procedure then count thirteen rows down and do it again until it hits an empty row. Ideally I could set it up to Find a certain cell entry and select 11 rows once it locates the next instance of this cell entry, but I think the 13 rows remains as a pattern so if it just counts down 13 rows that should be fine.

So, I think my quandary is how to make the row selections variable. When I look into the code I can see that my row selections are concrete values so if I were to run the Macro it wouldn't move down, it would just do the cells I have indicated. How can I make it move down through the cells?

Answer 1

You don't need to activate a workbook to do something with a range in it, rather you can do something with a range in a non-active workbook by qualifying what workbook the range is in first. The same applies to worksheets and ranges. Admittedly that is not what is causing problems in the code-sample you are posting, since you're really not touching on that, but it is making your code a lot harder to read.

One possible solution to your problem is a macro looking something like this:

Sub Copy_Paste
    Dim sourceBook As Workbook, destBook As Workbook, sourceRange As Range, destRange As Range

    Set sourceBook = Workbooks("Audubon-Crest-Rent-Roll-201502-1.xlsx")
    Set destBook = Workbooks("Audubon Crest Rent Roll 20150219-altered.xlsm")

    Set sourceRange = sourceBook.Worksheets(<name of sheet the data is in as string>).Range("A228:A239")
    Set destRange = destBook.Worksheets(<name of sheet the data shall be copied to as string>).Range("B17")

    Do
        sourceRange.Copy
        destRange.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        Application.CutCopyMode = False

        Set destRange = destRange.Offset(1, 0)
        Set sourceRange = sourceRange.Offset(13, 0)
    Loop Until IsEmpty(sourceRange)
End Sub

As you can see I define the ranges which shall be copied, by naming them and then telling Excel where they are by using the Set statement. For each iteration of the loop I then move the destination range one row further down by setting it again, and letting it be offset by one row from the previous destination range, by using the Offset-property. I do the same with the source-range, but I am offsetting it by 13 rows, as that was what it looked like you were doing in your code-sample.

I hope this helps!