Copy-error

[Francoise]

What's wrong with the blue line ?
Thanks in advance.

Sub macro1()
Dim Lastr1 As Integer, Lastr2 As Integer
Lastr1 = Sheets("Woman").Cells(Rows.Count, 1).End(xlUp).Row
Lastr2 = Sheets("Men").Cells(Rows.Count, 1).End(xlUp).Row
With Sheets("Sheet3")
Sheets("Woman").Range(Cells(2, 1), Cells(Lastr1, 1)).Copy .Cells(2, 1)
Sheets("Men").Range(Cells(2, 1), Cells(Lastr2, 1)).Copy .Cells(Lastr1 + 1, 1) 'ERROR
End With
End Sub

 

[Andrew Poulsom]

If worksheet men isn't the ActiveSheet you need to qualify the Cells property as well as the Range property:

Sheets("Men").Range(Sheets("Men").Cells(2, 1), Sheets("Men").Cells(Lastr2, 1)).Copy .Cells(Lastr1 + 1, 1)

 

[Francoise]

Ah, I see. I didn't know that.
Thank you very much Andrew !

 

[pgc01]

Hi

Notice that you have the same syntax error in the statement before:

Sheets("Woman").Range(Cells(2, 1), Cells(Lastr1, 1)).Copy .Cells(2, 1)

This would only work if when the code runs sheets Woman is the activesheet.

It's safer if you also correct it.

 

[Francoise]

Good remark pgc01, I will change that too.
Thank you !