Loops in VBA? I want to use a loop to select and copy till last cell

Question

I am trying to select each consecutive cell in Row K (starting from Range K1), and for each cell going down, copy the value and paste it into Cell M10. However, the way the macro is written currently, the macro is selecting the cell right below the last cell in Range K, and is thus copying a blank into M10. Instead, I want the loop to work down one cell at a time. I want to select one cell at a time and copy it, i.e. the Loop will select K1 and copy it to M10, then select K2 and copy it to M10, etc, and then have the loop stop after the last cell of Range K.

Can anyone please help me out on this?

Sub test() 

lastcell = Range("K" & Cells.Rows.Count).End(xlUp) 

Range("K2").Select 
Do 
ActiveCell.Offset(1, 0).Select 
Selection.Copy 
Range("M10").Select 
Selection.PasteSpecial
Application.Run ("Second Macro") 
Loop Until IsEmpty(ActiveCell.Value) 

End Sub

Answer 1

You can loop through column K using the small script below:

Option Explicit
Sub LoopThroughColumnK()

Dim LastRowInColK As Long, Counter As Long
Dim SourceCell As Range, DestCell As Range
Dim MySheet As Worksheet

'set references up-front
Set MySheet = ThisWorkbook.Worksheets("Sheet1")
With MySheet
    LastRowInColK = .Range("K" & .Rows.Count).End(xlUp).Row
    Set DestCell = .Range("M10")
End With

'loop through column K, copying from cells(counter, 11) to M10
With MySheet
    For Counter = 1 To LastRowInColK
        Set SourceCell = .Range("K" & Counter)
        SourceCell.Copy Destination:=DestCell
        'call MyMacro below
        '... doing cool MyMacro stuff
    Next Counter
End With

End Sub

To summarize what's happening, we:

1. Assign a worksheet variable to make sure we're working on the right sheet
2. Assign easy-to-read and reference variables for the last row and cell M10
3. Loop through the range in question, copying and pasting from Kn to M10

This technique also avoids using .Select, a common source of run-time errors. Here's an AMAZING post outlining lots of ways to NOT use .Select and .Activate: How to avoid using Select in Excel VBA macros

---

Edit: The refactoring I described in my comment above could be implemented without too much struggle. Let's break the whole problem into two bite-size chunks:

1. Get all the occupied cells in column K and save them as a Range
2. Running your secondary macro, which was keyed off cell M10, for each Cell in the Range we saved in step #1 above. We'll call the secondary macro MyOtherMacro for now

Let's get after it. Sunday Funday y'all! The code below is heavily-commented to explain what's happening in each function and step:

Option Explicit
Sub DoWork()

Dim MySheet As Worksheet
Dim ColKRange As Range

'set the worksheet we want to work on, in this case "Sheet1"
Set MySheet = ThisWorkbook.Worksheets("Sheet1")

'get the range of occupied cells in col K
Set ColKRange = OccupiedRangeInColK(MySheet)

'kick off the other macro using the range we got in the step above
Call MyOtherMacro(ColKRange)

End Sub

DoWork (above) is a "controller"-type script. All it does is kick off the other two functions we have written below, OccupiedRangeInColK and then, one step later, MyOtherMacro.

'this function returns a range object representing all
'the occupied cells in column K, starting at row 1 and ending
'at the last occupied row (in column K)
Public Function OccupiedRangeInColK(TargetSheet As Worksheet) As Range
    Dim LastRow As Long

    'check for unassigned worksheet object, return nothing if that's the case
    If TargetSheet Is Nothing Then
        Set OccupiedRangeInColK = Nothing
    End If

    With TargetSheet
        LastRow = .Range("K" & .Rows.Count).End(xlUp).Row
        Set OccupiedRangeInColK = .Range(.Cells(1, 11), .Cells(LastRow, 11))
    End With
End Function

Cool -- descriptive names are a great thing when it comes to scripting. OccupiedRangeInColK (above) takes a Worksheet, then returns the occupied Range from column K.

'this function is a shell to be populated by @polymorphicicebeam
Public Function MyOtherMacro(TargetRange As Range)
    Dim Cell As Range

    'check for an empty range, exit the function if empty
    If TargetRange Is Nothing Then Exit Function

    'loop through all the cells in the passed-in range
    For Each Cell In TargetRange
        'Do cool stuff in here. For demo purposes, we'll just
        'print the address of the cell to the screen
        MsgBox (Cell.Address)
    Next Cell
End Function

Finally, MyOtherMacro (above) is where you get to add your own magic. I built a "shell" function for you, which simply prints the address of the cell in question with a MsgBox. You can add your own logic where indicated inside the For Each Cell In TargetRange loop. Woo!