VBA copy paste into new range

[ChelseaH]

Hi,
I have the below code that copy and pastes full rows from one sheet to another based on certain criteria. It's currently pasting into column A on the next blank row but I would like it to paste in column B. I've taken this from a sheet I created years ago so apologies if it's messy! Is anyone able to help please?


Private Sub CommandButton1_Click()

a = Worksheets("time analyser").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To a

If Worksheets("time analyser").Cells(i, 2).Value = "BaU 2022 - Fortnightly planning calls (strat support)" Then

Worksheets("time analyser").Rows(i).Copy
Worksheets("BAU strategic support").Activate
B = Worksheets("BAU strategic support").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("BAU strategic support").Cells(B + 1, 1).Select
ActiveSheet.Paste
Worksheets("time analyser").Activate

End If
Next

Application.CutCopyMode = False

ThisWorkbook.Worksheets("time analyser").Cells(1, 1).Select

MsgBox ("Fortnightly planning calls ready")

End Sub

Thank you!

 

[Coyotex3]

Maybe changing this

Worksheets("BAU strategic support").Cells(B + 1, 1).Select

To this:

Worksheets("BAU strategic support").Cells(B + 1, 2).Select

 

[ChelseaH]

Thank you, when I try that I get: 'Runtime error 1004. You can't paste this here because the copy and paste area aren't the same size.'
Thanks for your help!

 

[Fluff]

Hi & welcome to MrExcel.
You cannot copy an entire row & paste it starting in col B as it would extend off the edge of the sheet. What is the range you need to copy?

 

[Coyotex3]

Hi & welcome to MrExcel.
You cannot copy an entire row & paste it starting in col B as it would extend off the edge of the sheet. What is the range you need to copy?

Thanks Fluff, I did not know that either.