Copy-error

Francoise

New Member
Joined
Feb 1, 2007
Messages
36
What's wrong with the blue line ?
Thanks in advance.
Rich (BB code):
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
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
If worksheet men isn't the ActiveSheet you need to qualify the Cells property as well as the Range property:

Rich (BB code):
Sheets("Men").Range(Sheets("Men").Cells(2, 1), Sheets("Men").Cells(Lastr2, 1)).Copy .Cells(Lastr1 + 1, 1)
 

Francoise

New Member
Joined
Feb 1, 2007
Messages
36
Ah, I see. I didn't know that.
Thank you very much Andrew !
 
Last edited:

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,823
Hi

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

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

Watch MrExcel Video

Forum statistics

Threads
1,108,614
Messages
5,523,906
Members
409,542
Latest member
Shezz01

This Week's Hot Topics

Top