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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,050
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Why have you got the .SpecialCells(12) at the end of Sheet3.Range("A" & Rows.Count).End(xlUp)? try replacing it with .Offset(1).
 
Solution

hsolanki

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

 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,050
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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

Board Regular
Joined
Jan 16, 2020
Messages
124
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Hi Mark No problem Many thanks for all your help :)

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

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
124
Office Version
  1. 2010
Platform
  1. Windows
Hi Mark

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

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,050
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

What is the result of
VBA Code:
MsgBox Sheet3.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
and
VBA Code:
MsgBox Range(r(1), rWC).Resize(, 3).SpecialCells(12).Rows.Count
 

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
124
Office Version
  1. 2010
Platform
  1. Windows
Hi Mark i get an error before your 2 msgbox suggestions. below line its where i get an error

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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,050
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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

Board Regular
Joined
Jan 16, 2020
Messages
124
Office Version
  1. 2010
Platform
  1. Windows
value on msgbox i is 1 and when i tried for r it says Type Mismatch. oh sorry i did not know this
 

Watch MrExcel Video

Forum statistics

Threads
1,129,592
Messages
5,637,290
Members
416,962
Latest member
samfuge

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