Copy to other sheet.

[Belleke]

Hello,
I have this code
Columns are switched.

With Sheets("Sheet1")
    .Columns(6).Copy Destination:=Sheets("Sheet2").Columns(1)
    .Columns(1).Copy Destination:=Sheets("Sheet2").Columns(2)
    .Columns(7).Copy Destination:=Sheets("Sheet2").Columns(3)
    .Columns(8).Copy Destination:=Sheets("Sheet2").Columns(4)
End With

But I need extra code.
The copy of sheet 1 should start a row 28 to the last used row.
rows 1 to 27 not copied)
Pasting should start at row 2 in sheet 2
The pasting should be values only
(PasteSpecial xlPasteValues)

Thanks in advance to helping me out

 

[Chihiro]

So first you need to define copy range limit. Either in With statement or after it.

As well, PasteSpecial isn't method/argument within copy operation, so it must be in it's own line/statement. (or use ":", a statement delimiter).

Ex:

With Sheets("Sheet1")
    With .Range("A28:H" & .Cells(Rows.Count, 1).End(xlUp).Row)
        .Columns(6).Copy: Sheets("Sheet2").Cells(2, 1).PasteSpecial xlPasteValues
        .Columns(1).Copy: Sheets("Sheet2").Cells(2, 2).PasteSpecial xlPasteValues
        .Columns(7).Copy: Sheets("Sheet2").Cells(2, 3).PasteSpecial xlPasteValues
        .Columns(8).Copy: Sheets("Sheet2").Cells(2, 4).PasteSpecial xlPasteValues
    End With
End With

 

[Belleke]

Hi Chihiro, thanks.
Works like a charm
Problem solved.