copied data overwriting on last row instead of next blank row

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
124
Office Version
  1. 2010
Platform
  1. Windows
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

VBA 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

VBA Code:
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,050
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Try r.Cells.Count or r.Rows.Count
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
124
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,050
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Can you describe exactly in words what range you are trying to copy please.
 

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
124
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,592
Messages
5,637,291
Members
416,963
Latest member
zazama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top