Copying rows or columns from one worksheet to another worksheet can be easily done using VBA. However, there are some instances in which you may want to give a special consideration in the output format or style. For example, you may want to copy all the rows or columns from one worksheet to another worksheet, but with one extra blank row inserted after each data row copied from the source worksheet. Please see the sample image as shown below:

Source worksheet

On the source worksheet, there are 10 rows and 2 columns with data. Notice that there are no blank rows between these data as shown in the image above.

Target worksheet

Here the target worksheet has a single blank row between the rows of data as a result of using VBA programming. This blank row is automatically added by the program through simple condition.

Below is the Excel VBA code to achieve the above-mentioned result:

Sub CopyRows()
Dim Ctr, NextRow, FinalRow As Integer
Sheets("Sheet1").Select
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
For Ctr = 1 To FinalRow
Cells(Ctr, 1).Resize(1, 33).Copy
Sheets("Sheet2").Select
NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(NextRow + 1, 1).Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Next Ctr
End Sub


VBA code analysis highlights:
Line# 3: Select or set the source worksheet (with name “Sheet1”) as the active sheet
Line# 4: Find the row number of last row under column A from the source worksheet and store the value into a variable named FinalRow
Line# 6: Copy the desired row (with horizontal row length = 33 cells) based on the for-loop sequence
Line# 7: Select or set the target worksheet (with name “Sheet2”) as the active sheet
Line# 8: Identify the row number of last blank row under column A on the target worksheet. Store the value into a variable named NextRow
Line# 9: Select or highlight the target row where the source data row will then be pasted. Here we added the condition “NextRow + 1” in order to skip one blank row
Line# 10: Reflect the row data that was copied from the source worksheet
Line# 11: Select or set the source worksheet  (with name “Sheet1”) as the active sheet again to prepare for the execution of the next for-loop
For any additional information on how to work with worksheets using VBA program manipulation, please refer to this link: https://software-solutions-online.com/excel-vba-working-with-sheets/
If you are working on retrieving data from another workbook, you can also refer to this link: https://software-solutions-online.com/excel-vba-get-data-from-another-workbook-through-automation/
Another sample scenario has a little complicated condition to consider in copying rows from one worksheet to another worksheet.
Assuming you have a time log workbook with one worksheet where the daily assignee enters their names with the corresponding date they work for the specific day, as shown in the sample image below. Values of dates are entered in column A while the names of assignees are entered under seven calendar days from Monday to Sunday. In the sample data shown below, there is only one assignee name entered on each calendar day.
Finally, the administrator would like to have a simplified summary sheet, in which the report should be displayed in column sequences as Date (under column A), Assignee Name (under column B) and Day (under column C), which is similar to the image as shown below:
The Excel VBA code to achieve the above-mentioned result:
Sub CopyRows_WithCondition()
Dim lastRow As Long
Dim col As Long
Dim ColA, ColB, ColC As String
With Sheets("Sheet1")
lastRow = .Range("B" & Rows.Count).End(xlUp).Row
For i = 4 To lastRow
For col = 3 To 9
If .Cells(i, col) <> "" Then
ColA = .Cells(i, 2)
ColB = .Cells(i, col)
ColC = .Cells(2, col)
With Sheets("Sheet2")
.Range("A" & .Range("A" & .Rows.Count).End(xlUp).Row + 1).Value = ColA
.Range("B" & .Range("B" & .Rows.Count).End(xlUp).Row + 1).Value = ColB
.Range("C" & .Range("C" & .Rows.Count).End(xlUp).Row + 1).Value = ColC
End With
End If
Next
Next
End With
End Sub


VBA code analysis highlights:
Line# 5: Set the source worksheet  (with name “Sheet1”) using a with-statement
Line# 6: Get the row number of last row under column B with data from the source worksheet and store the value into a variable named lastRow
Line# 7: Make a for-loop for to loop through all the target rows (from row 4 to the last row) to be copied and transferred to another worksheet
Line# 8: Make another for-loop to check the values from column C to I. These are the column regions which reside day values from Monday to Sunday
Line# 9: Check whether there is assignee name recorded from Monday to Sunday in the target cell. If the cell contains any non-empty record, execute the next series of VBA programming instructions under the given if-then statement.
(The following VBA code lines will be executed if the if-then statement in Line#9 is verified to be true)
Line# 10: Store the value of date in a variable named ColA
Line# 11: Store the value of Assignee name in a variable named ColB
Line# 12: Store the value of Day in a variable named ColC
Line# 13: Set the target worksheet  (with name “Sheet2”) using a with-statement
Line# 14: Put the date value stored in variable ColA to the last blank row under column A in target worksheet
Line# 15: Put the Assignee name stored in variable ColB to the last blank row under column B in target worksheet
Line# 16: Put the Day stored in variable ColC to the last blank row under column C in target worksheet
These sample VBA codes can be customized depending on the expected results of the users. By just simply changing the values of worksheet names, column or row addresses, similar results can thus be achieved. As a takeaway note, this is not just limited for copying data between worksheets but also applicable for copying rows or columns from one workbook to another workbook.