Copy certain columns form a workbook to another with Macro

[jevi]

Dear All,

I am trying to copy the data from the workbook "Report 1", sheet: "First" , only the columns "A:A,J:J,U:U,V:V,W:W,Z:Z,AG:AG,AW:AW,BB:BB"
to the Workbook "Report 2.xlsm, Sheets"All" to the A2:K


But is not working as it is copying other columns that are close to each other...what I am doing wrong with the VBA?!

Thank you,

Sub copyCols()

Application.ScreenUpdating = False
Dim LastRow As Long
Workbooks("Report 1.xlsx").Activate
LastRow = Sheets("Report 1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Sheets("First").Range("A:A,J:J,U:U,V:V,W:W,Z:Z,AG:AG,AW:AW,BB:BB").EntireColumn.Hidden = True
Range("A2:K" & LastRow).SpecialCells(xlCellTypeVisible).Copy Workbooks("Report 2.xlsm").Sheets("All").Cells(2, 1)
Sheets("First").Range("A:A,J:J,U:U,V:V,W:W,Z:Z,AG:AG,AW:AW,BB:BB").EntireColumn.Hidden = False
Application.ScreenUpdating = True

End Sub

 

[Fluff]

I'm confused, you say you want to copy columns A,J,U,V etc, but in your code, you are hiding those columns & then copying visible cells?

 

[jevi]

UPS.....that is the error...I should write the columns i want to hide. I will try now and see if it works . Sorry I am working the hole day so I am ?

 

[Fluff]

There's no need to hide columns, you can use something like

With Sheets("First")
   Intersect(.UsedRange, .Range("A:A,J:J,U:U,V:V,W:W,Z:Z,AG:AG,AW:AW,BB:BB")).Copy Workbooks("Report 2.xlsm").Sheets("All").Cells(2, 1)
End With

 

[jevi]

Thank you so much Pluff as that was the error and it worked as a treat . So happy after longs hours of work.

Have a nice weekend.

 

[Fluff]

You're welcome & thanks for the feedback

 

[jevi]

I did try your code Pluff and it worked great)...yes I am keeping yours as is easier visible cell and not hide columns.

Thank you a lot.

Sub copyCols()

Application.ScreenUpdating = False
Dim LastRow As Long
Workbooks("Report 1.xlsx").Activate
With Sheets("First")
Intersect(.UsedRange, .Range("A:A,J:J,U:U,V:V,W:W,Z:Z,AG:AG,AW:AW,BB:BB")).Copy Workbooks("Report 2.xlsm").Sheets("All").Cells(2, 1)
End With
End Sub

 

[Fluff]

 

[jevi]

Hi Pluff,

I try to use your code for another file and is not working right as the columns to copy are not in the order asa you see in the macro W:W is before J:J but the macro is copying J and not W first.

Please is there a way to fix that? Than it copies also the header but I don't need that as it will be always the same. And last wish...if in the new file Value.xlsm I already have formatted that and I will use that format but I see when I copy the value it doesn't keep my format...is there a way to keep the format?

Thank you.

Sub copyCols()

Application.ScreenUpdating = False
Dim LastRow As Long
Workbooks(" Dati.xlsx").Activate
With Sheets("Hi")
Intersect(.UsedRange, .Range("A:A,W:W,J:J,U:U,V:V,AC:AC,AD:AD,Z:Z,AG:AG,AV:AV,AW:AW,BB:BB,AX:AX")).Copy Workbooks("Value.xlsm").Sheets("Sample").Cells(2, 1)
End With
End Sub

 

[Fluff]

How about

Sub copyCols()

Application.ScreenUpdating = False
Dim Ary As Variant
Workbooks(" Dati.xlsx").Activate
With Sheets("Hi")
   Ary = Application.Index(.UsedRange.Offset(1), Evaluate("row(1:" & .UsedRange.Rows.Count - 1 & ")"), Array(1, 23, 10, 21, 22, 29, 30, 26, 33, 48, 49, 54, 50))
   Workbooks("Value.xlsm").Sheets("Sample").Cells(2, 1).Resize(UBound(Ary), UBound(Ary, 2)).Value = Ary
End With
End Sub