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]

Why have you got the .SpecialCells(12) at the end of Sheet3.Range("A" & Rows.Count).End(xlUp)? try replacing it with .Offset(1).

 

[hsolanki]

Hi Mark Thank you for your speedy reply and it has worked perfectly.

would you also help me with my another below post which i am really struggling to figure it out if you don't mind and have time

image hyperlink inserted from User form wont open

Hi i have got userform whereby user select the on the image and it changes to hyperlink and add the hyperlink to a cell however when tried opening the hyerlink it wort open, can someone point me in the right direction please. Private Sub CommandButton3_Click() On Error GoTo errHandler...

www.mrexcel.com

 

[MARK858]

You're welcome, I am afraid that I don't have time to look at your other post as I am leaving for work in 20 minutes.

 

[hsolanki]

Hi Mark No problem Many thanks for all your help

whenever you have time would you kindly have it look for me

 

[hsolanki]

Hi Mark

sorry once again but i have got an Run time error 6 "Overflow" when i changed the suggestion to .Offset(1)

 

[MARK858]

What is the result of

MsgBox Sheet3.Range("A" & Rows.Count).End(xlUp).Offset(1).Row

and

MsgBox Range(r(1), rWC).Resize(, 3).SpecialCells(12).Rows.Count

 

[hsolanki]

Hi Mark i get an error before your 2 msgbox suggestions. below line its where i get an error

 If i = 100 Or i = r.Count Then Exit For

i get that error if the same names on sheet 3 it is 3 times however it works fine with one or 2 times

 

[MARK858]

What are the values of i and r.Count when it errors? Btw, if it errors there then it is nothing to do with the Offset(1)

 

[hsolanki]

value on msgbox i is 1 and when i tried for r it says Type Mismatch. oh sorry i did not know this