copy & paste to end row with spaces with an offset

pook_666

Board Regular
Joined
Aug 16, 2018
Messages
83
Hi magicians,

Pretty simple answer I am sure, but can't seem to work it out!

I have the below snippet of code where I am trying to paste info to the current sheet. The pasting I'm trying to happen is at the end of the sheet plus 2 rows (hence the offset).

For some reason when it pastes, it is always pasting to row 8 even if A6 isn't the last row. E.g. before pasting, the last tow of the sheet is row 40, but will still paste over rows 7 & 8 instead of pasting in row 42.

Believe it is to do with this, but cannot seem to work it out:
VBA Code:
Offset(0).Resize(.Rows.Count - 0, 13).SpecialCells(12).Copy Sheets(MyCell.Value).Range("A6:A" & lrow2).Offset(2, 0)

Any help is greatly appreciated!

FYI there is no data in cells A1:A5 and from A6 there will be data with no spaces.

VBA Code:
            lrow = Sheets("Special Commission - With No CH").Range("A1").End(xlUp).Row
            lrow2 = Cells(Rows.Count, "A").End(xlUp).Row
            With Sheets("Special Commission - With No CH").Range("A1:A" & Sheets("Special Commission - With No CH").Range("A" & Rows.Count).End(xlUp).Row)
                .AutoFilter 1, MyCell.Value
                On Error Resume Next
                .Offset(0).Resize(.Rows.Count - 0, 13).SpecialCells(12).Copy Sheets(MyCell.Value).Range("A6:A" & lrow2).Offset(2, 0)
                On Error GoTo 0
                .AutoFilter
            End With
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,601
Office Version
  1. 365
Platform
  1. Windows
From initial glance your problem looks to be you paste into Sheets(MyCell.Value) yet when you assign the lrow2 variable you dont state Sheets(MyCell.Value) so use activesheet. So change to :

VBA Code:
lrow2 = Sheets(MyCell.Value).Cells(Sheets(MyCell.Value).Rows.Count, "A").End(xlUp).Row
 

pook_666

Board Regular
Joined
Aug 16, 2018
Messages
83
From initial glance your problem looks to be you paste into Sheets(MyCell.Value) yet when you assign the lrow2 variable you dont state Sheets(MyCell.Value) so use activesheet. So change to :

VBA Code:
lrow2 = Sheets(MyCell.Value).Cells(Sheets(MyCell.Value).Rows.Count, "A").End(xlUp).Row

Thanks for the help, unfortunately, I still have the same issue where it will just paste into row 8 instead of two rows after the last row in the sheet :/
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,545
Office Version
  1. 365
Platform
  1. Windows
You are offsetting 2 cells from the top of the range A6:Alrow2, hence the start at A8
You want to offset 2 cells from the bottom of that range.
So try

VBA Code:
.Offset(0).Resize(.Rows.Count - 0, 13).SpecialCells(12).Copy Sheets(MyCell.Value).Range("A" & lrow2).Offset(2, 0)
 
Solution

pook_666

Board Regular
Joined
Aug 16, 2018
Messages
83
You are offsetting 2 cells from the top of the range A6:Alrow2, hence the start at A8
You want to offset 2 cells from the bottom of that range.
So try

VBA Code:
.Offset(0).Resize(.Rows.Count - 0, 13).SpecialCells(12).Copy Sheets(MyCell.Value).Range("A" & lrow2).Offset(2, 0)

So strange, I swear I tried this and didn't work! ...must have not done something correctly.

However, this works perfectly - thank you very much!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,545
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the follow-up. :)
 

Forum statistics

Threads
1,141,128
Messages
5,704,446
Members
421,350
Latest member
jake9951

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