+- QP School (https://qomplainerzschool.lima-city.de)
+-- Forum: Tutorials (https://qomplainerzschool.lima-city.de/forumdisplay.php?fid=3)
+--- Forum: Excel VBA Tutorials (https://qomplainerzschool.lima-city.de/forumdisplay.php?fid=48)
+--- Thread: Copy data to another worksheet (/showthread.php?tid=5271)
Copy data to another worksheet - Qomplainerz - 07-27-2023
Example:
Sub CopyDataToAnotherSheet()
Dim sourceWs As Worksheet
Dim targetWs As Worksheet
Dim lastRowSource As Long
Dim lastRowTarget As Long
'Set the source worksheet where the data is located
Set sourceWs = ThisWorkbook.Sheets("SourceSheet")
'Set the target worksheet where the data will be copied
Set targetWs = ThisWorkbook.Sheets("TargetSheet")
'Find the last row with data in the source worksheet
lastRowSource = sourceWs.Cells(sourceWs.Rows.Count, "A").End(xlUp).Row
'Find the last row with data in the target worksheet
lastRowTarget = targetWs.Cells(targetWs.Rows.Count, "A").End(xlUp).Row
'Copy the data from the source worksheet to the target worksheet
sourceWs.Range("A1:A" & lastRowSource).Copy targetWs.Range("A" & lastRowTarget + 1)
End Sub
Explanation:
The code defines a subroutine named CopyDataToAnotherSheet.
It declares variables for the source worksheet (sourceWs), the target worksheet (targetWs), and the last row with data in each worksheet (lastRowSource and lastRowTarget).
Set sourceWs = ThisWorkbook.Sheets("SourceSheet") specifies the source worksheet ("SourceSheet") where the data is located.
Set targetWs = ThisWorkbook.Sheets("TargetSheet") specifies the target worksheet ("TargetSheet") where the data will be copied.
The lastRowSource variable is determined using sourceWs.Cells(sourceWs.Rows.Count, "A").End(xlUp).Row, which finds the last row with data in column A of the source worksheet.
The lastRowTarget variable is determined using targetWs.Cells(targetWs.Rows.Count, "A").End(xlUp).Row, which finds the last row with data in column A of the target worksheet.
The data from column A of the source worksheet (from A1 to the last row with data) is copied to the target worksheet starting from the next empty row in column A.