CODEWITHSUNDEEP
vbaexcel
aicirtap
aicirtap

Reputation: 111

VBA- Copy specific columns to a sheet from a workbook

i need some help how to fix my syntax. Whenever i try to run it there is an error saying "subscript out of range"

I need to copy columns ("B:F"),("J"),(N:Q), (S:V) from Sheet("Filtered Data") to a workbook Sheet("February 2018 Tracker (Raw)")

When i deleted the selected columns ("J"),(N:Q), (S:V) the code is working and copying the data from columns B2:F2.

I know there is something wrong with my syntax but i can't figure it out how to correct it. Please help.

Thanks

Sub L4toMetrics()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim MainWorkfile As String
Dim OtherWorkfile As String

MainWorkfile = ActiveWorkbook.Name

lRow = Range("C1048576").End(xlUp).Row

    Sheets("February 2018 Tracker (Raw)").Select
    Range("B2:Q2" & lRow).ClearContents
    Range("C1").Select

    Application.AskToUpdateLinks = False
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    Workbooks.Open Filename:=Application.GetOpenFilename
    OtherWorkfile = ActiveWorkbook.Name

    Sheets("Filtered Data").Select
    If ActiveWorkbook.ActiveSheet.FilterMode Or _
       ActiveWorkbook.ActiveSheet.AutoFilterMode Then _
       ActiveWorkbook.ActiveSheet.AutoFilterMode = False
    lRw = Range("C1048576").End(xlUp).Row

    Range("B2:F2" & lRw).Select
    Selection.Copy

    Windows(MainWorkfile).Activate
    Sheets("February 2018 Tracker (Raw)").Select
    lstrw = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row + 1
    Range("B" & lstrw).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False



   Windows(OtherWorkfile).Activate
    Sheets("Filtered Data").Select
    If ActiveWorkbook.ActiveSheet.FilterMode Or _
       ActiveWorkbook.ActiveSheet.AutoFilterMode Then _
       ActiveWorkbook.ActiveSheet.AutoFilterMode = False
    lRw = Range("C1048576").End(xlUp).Row

    Range("J2" & lRw).Select
    Selection.Copy

    Windows(MainWorkfile).Activate
    Sheets("February 2018 Tracker (Raw)").Select
    lstrw = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row + 1
    Range("C" & lstrw).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False



   Windows(OtherWorkfile).Activate
    Sheets("Filtered Data").Select
    If ActiveWorkbook.ActiveSheet.FilterMode Or _
       ActiveWorkbook.ActiveSheet.AutoFilterMode Then _
       ActiveWorkbook.ActiveSheet.AutoFilterMode = False
    lRw = Range("C1048576").End(xlUp).Row

    Range("N2:Q2" & lRw).Select
    Selection.Copy

    Windows(MainWorkfile).Activate
    Sheets("February 2018 Tracker (Raw)").Select
    lstrw = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row + 1
    Range("D" & lstrw).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False



  Windows(OtherWorkfile).Activate
    Sheets("Filtered Data").Select
    If ActiveWorkbook.ActiveSheet.FilterMode Or _
       ActiveWorkbook.ActiveSheet.AutoFilterMode Then _
       ActiveWorkbook.ActiveSheet.AutoFilterMode = False
    lRw = Range("C1048576").End(xlUp).Row

    Range("S2:O2" & lRw).Select
    Selection.Copy

    Windows(MainWorkfile).Activate
    Sheets("February 2018 Tracker (Raw)").Select
    lstrw = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row + 1
    Range("D" & lstrw).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False




    Application.CutCopyMode = False
    Application.AskToUpdateLinks = True
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

End Sub

Upvotes: 2

Views: 708

Answers (1)

Shai Rado
Shai Rado

Reputation: 33682

You are relying too much on the MACRO-Recorder, try the code below to copy>>paste for the first section (columns "B:F").

You be able to implement it for the rest of the columns.

Option Explicit

Sub L4toMetrics()

Dim MainWorkfile As Workbook
Dim OtherWorkfile As Workbook
Dim TrackerSht As Worksheet
Dim FilterSht As Worksheet

Dim lRow As Long, lRw As Long

Application.ScreenUpdating = False
Application.DisplayAlerts = False

' set workbook object
Set MainWorkfile = ActiveWorkbook

' set the worksheet object
Set TrackerSht = MainWorkfile.Sheets("February 2018 Tracker (Raw)")
With TrackerSht
    lRow = .Cells(.Rows.Count, "C").End(xlUp).Row ' last row with data in column "C"
    .Range("B2:Q2" & lRow).ClearContents
End With

Application.AskToUpdateLinks = False

' set the 2nd workbook object
Set OtherWorkfile = Workbooks.Open(Filename:=Application.GetOpenFilename)

' set the 2nd worksheet object
Set FilterSht = OtherWorkfile.Sheets("Filtered Data")

With FilterSht
    If .FilterMode Or .AutoFilterMode Then .AutoFilterMode = False
    lRw = .Cells(.Rows.Count, "C").End(xlUp).Row ' last row with data in column "C"

    .Range("B2:F" & lRw).Copy ' copy your range
End With

' paste
TrackerSht.Range("B" & lRow).PasteSpecial Paste:=xlPasteValues, _
                Operation:=xlNone, SkipBlanks:=False, Transpose:=False

' implement it for the rest of your columns...


End Sub

Upvotes: 1

Related Questions