CODEWITHSUNDEEP
excelvba
kt190090
kt190090

Reputation: 49

After initial macro run, move source range to next row and copy

I was recently assisted by a member of this community in addressing how I should build out a macro for my project. The following macro works precisely as I would like it to. However, there is a manual dependency that I am trying to correct.

The source range is predefined as specific cell references (e.g. A10, B10, C10, F10...) After I run this macro, I would like the source range to move down to the next row so that the next time the macro is called, it copies A11, B11, C11, F11...

Please let me know if this is possible. The following is the VBA code I've been using:

Public Sub Update_Project_1()

    ' Set a reference to the source sheet
    Dim sourceSheet As Worksheet
    Set sourceSheet = ThisWorkbook.Worksheets("High Level Tracker")
    
    ' Set a reference to the target sheet
    Dim targetSheet As Worksheet
    Set targetSheet = ThisWorkbook.Worksheets("Project 1 Detailed Tracker")

    ' Set a reference to the source range
    Dim sourceRange As Range
    Set sourceRange = sourceSheet.Range("A10,B10,C10,F10,H10")

    ' Get last row in target sheet
    Dim lastRow As Long
    lastRow = targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Row

    ' Loop through each cell in source range
    Dim sourceCell As Range
    For Each sourceCell In sourceRange.Cells
        ' Output values from source range into next empty row in target
        Dim columnCounter As Long
        targetSheet.Range("A" & lastRow + 1).Offset(, columnCounter).Value = sourceCell.Value
        columnCounter = columnCounter + 1
    Next sourceCell

End Sub

Any help would be kindly appreciated, thanks!

Upvotes: 0

Views: 604

Answers (3)

Ricardo Diaz
Ricardo Diaz

Reputation: 5696

You can find the last empty row in the source sheet and then copy the values to the target sheet

Public Sub Update_Project_1()

    ' Set a reference to the source sheet
    Dim sourceSheet As Worksheet
    Set sourceSheet = ThisWorkbook.Worksheets("High Level Tracker")
    
    ' Set a reference to the target sheet
    Dim targetSheet As Worksheet
    Set targetSheet = ThisWorkbook.Worksheets("Project 1 Detailed Tracker")
    
    ' Get last row in source sheet
    Dim lastRowSource As Long
    lastRowSource = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row
    
    ' Define the source range address
    Dim sourceRangeAddress As String
    sourceRangeAddress = "A<r>,B<r>,C<r>,F<r>,H<r>"
    
    ' Replace next row in source rane
    sourceRangeAddress = Replace(sourceRangeAddress, "<r>", lastRowSource)
    
    ' Set a reference to the source range
    Dim sourceRange As Range
    Set sourceRange = sourceSheet.Range(sourceRangeAddress)
    
    ' Get last row in target sheet
    Dim lastRowTarget As Long
    lastRowTarget = targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Row

    ' Loop through each cell in source range
    Dim sourceCell As Range
    For Each sourceCell In sourceRange.Cells
        ' Output values from source range into next empty row in target
        Dim columnCounter As Long
        targetSheet.Range("A" & lastRowTarget + 1).Offset(, columnCounter).Value = sourceCell.Value
        columnCounter = columnCounter + 1
    Next sourceCell

End Sub

Upvotes: 1

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

Not the cleanest one, but it may help.

At start of your code, just add:

Dim ThisRow As Long
ThisRow = InputBox("What row?", , 10)

This will ask user in every execution of macro a row number (default value =10)

Then replace line

Set sourceRange = sourceSheet.Range("A10,B10,C10,F10,H10")

with

Set sourceRange = sourceSheet.Range("A" & ThisRow & ",B" & ThisRow & ",C" & ThisRow & ",F" & ThisRow & ",H" & ThisRow)

This way, every execution will allow you to choose what the target row, without editing code manually.

Upvotes: 0

Dominique
Dominique

Reputation: 17481

Your current cell is called ActiveCell. In order to go to another cell, you might use the Offset() function.

So, both combined give following line of source code:

ActiveCell.Offset(1,0).Activate

This means: take the current active cell, go one row further but no columns (1,0), and activate that cell.

Upvotes: 0

Related Questions