Append data to last row-CodePudding

I am quite new to VBA, hence unable to understand the scripts at this moment. I tried recording a macro and it does quite a good job. However, it's not dynamic.

Here is my use case:

I have an excel workbook and it has two sheets named "Sheet1" & "FinalData". All I want to do is copy the data from a specific cell range of let's say C2:P2 from "sheet1" and append it to the "FinalData" sheet.

Basically, find the last empty row and paste the data there. Below is my recorded piece of vba code that indicates this function.

Can anyone help me with fixing the below code or sharing a new code, please? I will be grateful to you.

Thanks!

Application.CutCopyMode = False
    Range("C2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("FinalData").Select
    Range("A2").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

CodePudding user response：

Try this code, to select the last empty row, just sum 1 to the total used rows

Application.CutCopyMode = False
Range("C2").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("FinalData").Select
Cells(Sheets("FinalData").UsedRange.Rows.Count   1, 1).Select ' Select last empty row
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

CodePudding user response：

Sub copyData()

Dim sheet1 As Worksheet
Dim finalDataSheet As Worksheet

Set sheet1 = Sheets("Sheet1")
Set finalDataSheet = Sheets("FinalData")

'Get the last column based on the second row.
lastcolumn = sheet1.Cells(2, Columns.Count).End(xlToLeft).Column

'Get last row based on column C.
lastrow = sheet1.Cells(Rows.Count, 3).End(xlUp).Row

'Option A:
'Only transfer row 2 with dynamic columns from C to end column.
finalDataSheet.Range(finalDataSheet.Cells(2, 3), finalDataSheet.Cells(2, 
lastcolumn)).Value = _
sheet1.Range(sheet1.Cells(2, 3), sheet1.Cells(2, lastcolumn)).Value

'Option B:
'Transfer entire dynamic table structure starting at c2.
'Get the values from sheet 1 data range and copy values over to final data 
sheet into the same position.
finalDataSheet.Range(finalDataSheet.Cells(2, 3), 
finalDataSheet.Cells(lastrow, lastcolumn)).Value = _
sheet1.Range(sheet1.Cells(2, 3), sheet1.Cells(lastrow, lastcolumn)).Value

End Sub