copying some rows and columns
Hi
I think this is what you need:
Sub aaa()
Dim TargetSh As Worksheet
Dim DestSh As Worksheet
Dim LastRow As Long
Application.ScreenUpdating = False
Set TargetSh = Worksheets("Sheet1")
Set DestSh = Worksheets("Sheet2")
LastRow = 252
With TargetSh
.Range("DM1:DM" & LastRow).AutoFilter field:=1, Criteria1:="<x"
.Range("A2:C" & LastRow).SpecialCells(xlCellTypeVisible).Copy
DestSh.Range("A2")
.Range("D2:D" & LastRow).SpecialCells(xlCellTypeVisible).Copy
DestSh.Range("N2")
.Range("AA2:AA" & LastRow).SpecialCells(xlCellTypeVisible).Copy
DestSh.Range("O2")
.Range("AK2:AL" & LastRow).SpecialCells(xlCellTypeVisible).Copy
DestSh.Range("D2")
.Range("AM2:AM" & LastRow).SpecialCells(xlCellTypeVisible).Copy
DestSh.Range("G2")
.Range("AN2:AN" & LastRow).SpecialCells(xlCellTypeVisible).Copy
DestSh.Range("F2")
.Range("AO2:AO" & LastRow).SpecialCells(xlCellTypeVisible).Copy
DestSh.Range("J2")
.Range("AP2:AP" & LastRow).SpecialCells(xlCellTypeVisible).Copy
DestSh.Range("H2")
.Range("AR2:AR" & LastRow).SpecialCells(xlCellTypeVisible).Copy
DestSh.Range("I2")
.Range("DM1:DM" & LastRow).AutoFilter
End With
Application.ScreenUpdating = True
End Sub
Regards,
Per
"Jack Sons" skrev i meddelelsen
...
Hi all,
Of sheet 1, rows 2 tot 252 and columns A to AR I want to copy
columns A to C to sheet 2 colums A to C rows 2 and further,
column D to sheet 2 colum N rows 2 and further,
column AA to sheet 2 column O rows 2 and further,
columns AK and AL to sheet 2 colums D and E rows 2 and further,
column AM to sheet 2 column G rows 2 and further,
column AN to sheet 2 column F rows 2 and further,
column AO to sheet 2 column J rows 2 and further,
column AP to sheet 2 column H rows 2 and further,
column AR to sheet 2 column I rows 2 and further,
and all that only for those rows where in column DM is not an "x".
Formats, text colors and interior colors are also to be copied.
Due to clumsy code (sorry for that) I can't change the order of the
columns in sheet 1 without getting an awful lot of trouble.
I'm looking for nice compact and fast executing code, your assistance will
be appreciated.
Jack Sons
The Netherlands
|