copy & paste to end row with spaces with an offset

[pook_666]

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:

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.

            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

 

[steve the fish]

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 :

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

 

[pook_666]

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 :

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]

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

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

 

[pook_666]

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

.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]

You're welcome. Thanks for the follow-up.