VBA- Copy specific columns to a sheet from a workbook

Question

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

1. Shouldnt code lines like `Range("B2:F2" & lRw).Select` be `Range("B2:F" & lRw).Select` ? 2. What line is the error on? 3. Read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — , Mar 18 '18 at 13:14
4. `Range("J2" & lRw).Select Selection.Copy` should be `Range("J2:J" & lRw).Copy` — , Mar 18 '18 at 13:20
Hi @Jeeped Range("B2:F2" & 1Rw).select is working fine. The line error is from Range("J2") to Range("S2:O2"). It does not read to copy the selected range. — aicirtap, Mar 18 '18 at 13:21
**tldr:** There appear to be several rudimentary syntax and typographical errors. Can you get started on those and [edit] your post to include the corrections? — , Mar 18 '18 at 13:22
If 1Rw is 100 then Range("B2:F2" & lRw) refernces B2:F2100 not B2:F100 so **no** it is not 'working fine'. — , Mar 18 '18 at 13:23

Shai Rado · Accepted Answer · 2018-03-18T13:35:54.103

1

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

edited Mar 18 '18 at 13:35

answered Mar 18 '18 at 13:24

Shai Rado

33,032
6
29
51

There is an error "object variable or with block variable not set" Line item (MainWorkfile = ActiveWorkbook) – aicirtap Mar 18 '18 at 13:35
@aicirtap you are right, fixed it now, try the modified code – Shai Rado Mar 18 '18 at 13:36
@aicirtap do you have any data in the sheet before pasting ? – Shai Rado Mar 18 '18 at 13:57
Hi @shai rado the data i have in the sheet is the column header only and at the column A there is a formula to determine what division is in the column C, other than that there is no other data at other columns. When i run the code it overlaps the column header which should be started at "B2" – aicirtap Mar 18 '18 at 22:21
Thanks Shai i already resolved the issue of overlapping, what i did is i started copying the range from B1:F since they are the same title of the header. Then i deleted (.Range("B2:Q2" & lRow).ClearContents) because whenever i try to run again the code it is pasted from the last row that have a data. I used (Worksheets("February 2018 Tracker (Raw)").Range("B2:S1023").ClearContents) from the start of the syntax. Thanks again – aicirtap Mar 19 '18 at 02:45

VBA- Copy specific columns to a sheet from a workbook

1 Answers1