Syntax Issue

[Mozzz]

I am trying to copy a selection on a sheet for Range("B2") to SpecialCells(xlLastCell). I think I should be able to do this but can't get the syntax right.

Here is my code:

Sheets(1).Range("B1:" & .SpecialCells(xlLastCell)).Copy _
        Destination:=Sheets(2).Range("A1")

Any suggestions Please

Mozzz

 

[VoG]

Try

Dim LR As Long
With Sheets(1)
    LR = .Range("B" & Rows.Count).End(xlUp).Row
    .Range("B2:B" & LR).Copy Destination:=Sheets(2).Range("A1")
End With

 

[Andrew Poulsom]

This worked for me:

    With Sheets(1)
        .Range(.Cells(1, 2), .Cells.SpecialCells(xlLastCell)).Copy Destination:=Sheets(2).Range("A1")
    End With

 

[Mozzz]

Both work, Perfect, Thanks So Much!! I am learning what VBA can do but struggle with the how.

Your help is greatly appreciated.

Mozzz