Need to copy columns H,K,L From one excel workbook to new workbook using Excel Macro

Question

I have a excel workbook A.xlsx with columns A through T, now i need to copy specific columns H,K,L to a new workbook which would be created while i run a macro.

I was able to successfully copy a range of columns from one worksheet to another, but i am not finding a way to copy specific columns to a new workbook.

Private Sub copy_sub()
Sheets("Sheet1").Columns("H:K").Copy Sheets("Sheet2").Range("A1")
End Sub

Solution 1

Give this a try:

Sub dural()
    Dim r1 As Range, r2 As Range
    Sheets("Sheet1").Select
    Set r1 = Range("K:L")
    Set r2 = Range("H:H")
    Set wbNew = Workbooks.Add
    r2.Copy Range("H1")
    r1.Copy Range("K1")
End Sub

Based on the fact that after the workbook is added, the new workbook will be active and Sheet1 will be the active sheet in that book. Also assumes that cols H,K,L are to be copied, but not col I.

Solution 2

Try below sample code

   Private Sub copy_sub()

    Dim wkb As Workbook
    Set wkb = Workbooks.Add    ' Will add new workbook

    ' with column name
    ThisWorkbook.Sheets("Sheet1").Columns("H").Copy wkb.Sheets("Sheet2").Range("A1")

    'with column index
    ThisWorkbook.Sheets("Sheet1").Columns(9).Copy wkb.Sheets("Sheet2").Range("A1")

End Sub