copied data overwriting on last row instead of next blank row

[hsolanki]

Hello everyone i have got the code whereby it copies from Sheet 2 and then pastes onto the sheet 3 blanks rows however problem is when there is a value e.g. 4 rows as got value it will overwrite on the last used row which is 4th row instead of pasting on to next available blank row. can someone kindly help me please and also if it can be pasted starting from 2nd row instead of 1st row so i can have the title. below is the code

Sub CopyRows()
Dim i As Integer
Dim iMessage As VbMsgBoxResult
    
Application.EnableCancelKey = xlDisabled

TopNRows

Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("EmailReport")
    ws1.Range("A1:C36").Copy
    
Sheets("EmailReport").Range("A1").Select

Mail_Selection_Range_Outlook_Body

Clear_All_Filters_Range
End Sub

and below it is the TopNRows where it actualy paste the data on to sheet3

Sub TopNRows()
Dim i As Long
Dim r As Range
Dim rWC As Range

Sheet2.Unprotect Password:="2021"
Sheet3.Unprotect Password:="2021"

Set r = Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(12)

For Each rWC In r
    i = i + 1
    If i = 100 Or i = r.Count Then Exit For
    Next rWC
  
Range(r(1), rWC).Resize(, 3).SpecialCells(12).Copy Sheet3.Range("A" & Rows.Count).End(xlUp).SpecialCells(12)
Sheet3.Protect Password:="2021"
Sheet2.Protect Password:="2022"
End Sub

 

[MARK858]

Try r.Cells.Count or r.Rows.Count

 

[hsolanki]

Morning Mark i have tried with the r.Rows.Count and it works however the problem is now that it is copying the first Title row only and not the actual data row.

 

[MARK858]

Can you describe exactly in words what range you are trying to copy please.

 

[hsolanki]

data are copied from sheet 2 A2 onward (data is on column A its name, b-types of training and C-expiry date on to the sheet 3.