Copy & Insert a Row using VBA

Question

I am very new to VBA and need some help. I want to copy Row 1 from Sheet3 of my workbook and insert it in the ReviewCoverSheet worksheet within the same workbook below the active cell (regardless of which column I am in on the active row) shifting all the other rows down. Below is the code I'm using, although I'm not too confident it will get me what I need. Whenever I run it, I get a Compile Error: Method or data member not found that highlights "ActiveCell" in the Set Destination Row (below active cell).

I also have Checkboxes in the line I am trying to copy over on Sheet3 that I would like to ensure come over when copied and inserted. Any help is greatly appreciated.

Sub CopyRowBelowActiveCell()

'\*\*Declare Variables\*\* 

Dim SourceSheet As Worksheet 

Dim DestinationSheet As Worksheet 

Dim SourceRow As Integer 

Dim DestinationRow As Integer 

'\*\*Set Source and Destination Sheets\*\* 

Set SourceSheet = ThisWorkbook.Sheets("Sheet3") 

Set DestinationSheet = ThisWorkbook.Sheets("ReviewCoverSheet") 

'\*\*Set Source Row\*\* 

SourceRow = 1 

'\*\*Set Destination Row (below active cell)\*\* 

DestinationRow = DestinationSheet.ActiveCell.Row + 1 

'\*\*Copy the Row\*\* 

SourceSheet.Rows(SourceRow).Copy Destination:=DestinationSheet.Rows(DestinationRow) 

End Sub

Answer 1

Just change one line:

'**Set Destination Row (below active cell)**

DestinationRow = ActiveCell.Row + 1

The activecell object does not use a sheet object...

But when you say 'insert' - that has a specific meaning, and what your code does is copy the values over the existing row, rather than 'inserting' the row... probably what you actually want but I thought I should mention it.

Answer 2

This worked almost perfectly. It is not inserting the row, it is pasting over anything that is in the row below the active cell. What do I need to change so that it inserts a row?

Answer 3

'**Set Destination Row (below active cell)**

DestinationRow = DestinationSheet.ActiveCell.Row + 1

'**Insert a new row for the copied values**

DestinationSheet.Rows(DestinationRow).Insert

'**Copy the Row**

SourceSheet.Rows(SourceRow).Copy Destination:=DestinationSheet.Rows(DestinationRow)

Answer 4

Ugh, I'm now getting the original error message:

Answer 5

Ooops! Sorry - I copied your original instead of the changed code:

'**Set Destination Row (below active cell)**

DestinationRow = ActiveCell.Row + 1

'**Insert a new row for the copied values**

DestinationSheet.Rows(DestinationRow).Insert

'**Copy the Row**

SourceSheet.Rows(SourceRow).Copy Destination:=DestinationSheet.Rows(DestinationRow)