Solved VBA: Copy Data to the Next Empty Row (Different Worksheets)

October 13, 2020 at 15:15:07
Specs: Windows 10
I am practicing VBA, and I tried copying a data from a table from Sheet 4 to a table in Sheet 5. Here is the code that I got:
Sub Macro26()
'
' Macro26 Macro
'

'
    ActiveCell.Offset(1, 0).Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Sheet5").Select
    ActiveCell.Offset(-33, -5).Range("A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(7, 0).Range("A1").Select
End Sub

Basically, I want to move the data from column A of Sheet 4 to Column A of Sheet 5. When I add a new data in the column A of Sheet 4, I want to move it to the next empty row in column A of Sheet 5. Please help me correct my code. I'm just starting Macro.

See More: VBA: Copy Data to the Next Empty Row (Different Worksheets)


#1
October 13, 2020 at 17:04:35
✔ Best Answer
The macro recorder will never get you where you want to go in a situation like this. Since the length of your columns keep changing, you have to have VBA determine the last row that contains data on Sheet4 and the next empty row on Sheet5 each time you make a change.

Something like this should work:

Sub CopyToNextRow()

Dim copyRow As Long, pasteRow As Long

'Determine last row with Data on Sheet 4 Column A
   copyRow = Sheets("Sheet4").Range("A" & Rows.Count).End(xlUp).Row

'Determine next empty row on Sheet 5 Column A
   pasteRow = Sheets("Sheet5").Range("A" & Rows.Count).End(xlUp).Row + 1
 
'Copy/Paste
   Sheets(4).Range("A" & copyRow).Copy _
       Sheets(5).Range("A" & pasteRow)

End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#2
October 13, 2020 at 17:34:07
BTW...depending on how you are entering your data, you may be able to make the copy/paste automatic.

Enter the data, hit enter or click out of the cell and the code will run automatically. You do that by using the Worksheet_Change event in VBA.

Let me know if you are interesting in hearing more about that.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •
Related Solutions


Ask Question