Excel VBA: Subscript out of range code fix

Question

I keep getting a subscript out of range error in the line

Sheets("Dump").Select

How can I adjust my code to remove the error? And is there a way to adjust this to remove the .Select

Sub UploadData()
'open the source workbook and select the source
Dim wb As Workbook

Workbooks.Open Filename:=Sheets("Instructions").Range("$B$4").value
Set wb = ActiveWorkbook
Sheets("Invoice Totals").Select

'copy the source range
Sheets("Invoice Totals").Range("A:R").Select
Selection.Copy

'select current workbook and paste the values
ThisWorkbook.Activate
Sheets("Dump").Select
Sheets("Dump").Range("A2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False

' copy the source range
Sheets("Lease & RPM Charges").Range("A:AH").Select
Selection.Copy

'select current workbook and paste the values
ThisWorkbook.Activate
Sheets("Dump").Select
Sheets("Dump").Range("T2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False

'copy the source range
Sheets("MMS_Service_And_Repairs").Range("A:R").Select
Selection.Copy

'select current workbook and paste the values
ThisWorkbook.Activate
Sheets("Dump").Select
Sheets("Dump").Range("BC2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False

'close the source workbook
wb.Close

End Sub

That usually happens if you are trying to select a hidden worksheet, or your activeworkbook doesn't have that sheet. Can you explain what do you want to achieve and how many workbooks are in play here? — Damian, Oct 05 '18 at 13:16
^^^ You can avoid this error by just not `Select`ing a worksheet. `ThisWorkbook.Sheets("Dump").Range("A2").PasteSpecial xlPasteValues` — urdearboy, Oct 05 '18 at 13:24
^ This is the same as the 4 lines you have to select a sheet, select a range, and then paste some values. Just notice nothing is selected. Less lines, less fuss — urdearboy, Oct 05 '18 at 13:25
Clean up your code and remove `Select" and `Activate`, Using `With` can help. Your Ranges are wrong unless you trying to copy the whole column. — GMalc, Oct 05 '18 at 13:30
I have three sheets in another file, and I want to copy all the data from those sheets into a new spreadsheet called Dump. How can i adjust my code to make it cleaner/shorter — eenz, Oct 05 '18 at 13:35
Be aware that the `Sheets` collection will include any Chart worksheets you may have in your workbook. Referring to `Worksheets` eliminates this possibility. (Not critical in this application since you're not looping through the `Sheets` collection, but something to be aware of.) Also, read [this post](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) to follow @GMalc's suggestion on eliminating `.Select` — FreeMan, Oct 05 '18 at 13:36
Do these sheets have headers? if so, do you want to copy them aswell? all the columns have the same rows? — Damian, Oct 05 '18 at 13:36
Also the third range you copy will be pasted over a portion(25 Columns) of the second range you copy. — GMalc, Oct 05 '18 at 13:43
Leena, please see my edit, sorry my first code was a problem, I was in a hurry. This edit should be good, unless i misunderstood what you wanted. If you have any problems please identify and i will help you fix. — GMalc, Oct 06 '18 at 06:04

GMalc · Accepted Answer · 2018-10-06T06:02:16.707

Edit(Fixed issues) Try this...(Tested on mock data).

Sub UploadData()

Dim wb As Workbook
Dim lRow As Long, lRow2 As Long, lRow3 As Long 'Set lastrow for each source worksheet
Dim rng As Range, rng2 As Range, rng3 As Range 'Set range for each source worksheet

'open the source workbook using the filename in cell B4(I'm making an assumption that the
'source workbook is located in the same folder as the Thisworkbook
Set wb = Workbooks.Open(Filename:=Sheets("Instructions").Range("$B$4").Value)

With wb
    With .Sheets("Invoice Totals") 'Copy the range on this ws and paste to "Dump" in dest ws
        lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        Set rng = .Range("A1:R" & lRow)
            rng.Copy Destination:=ThisWorkbook.Sheets("Dump").Range("A2")
    End With

    With .Sheets("Lease & RPM Charges") 'Copy the range on this ws and paste to "Dump" in dest ws
        lRow2 = .Cells(.Rows.Count, 1).End(xlUp).Row
        Set rng2 = .Range("A1:AH" & lRow2)
            rng2.Copy Destination:=ThisWorkbook.Sheets("Dump").Range("T2")
    End With

    With .Sheets("Invoice Totals") 'Copy the range on this ws and paste to "Dump" in dest ws
        lRow3 = .Cells(.Rows.Count, 1).End(xlUp).Row
        Set rng3 = .Range("A1:R" & lRow3)
            rng3.Copy Destination:=ThisWorkbook.Sheets("Dump").Range("BC2")
    End With

    With ThisWorkbook.Sheets("Dump").UsedRange
       .Value = .Value  'Sets all the data in the usedrange to values only
    End With
End With

wb.Close 'close the source workbook

End Sub

GMalc you could change ActiveWorkbook for wb since you declared it. that way will prevent for errors if the user touches excel and changes workbooks — Damian, Oct 05 '18 at 13:58
This has the same issue as previous solution. OP wants to paste values — urdearboy, Oct 05 '18 at 14:09
@Damian, @urdearboy, made changes, but the third copy paste will still overwrite the data from the second paste, unless the OP clarifies what he/she wants to accomplish. I'm using `UsedRange` because sheet "Dump" is new per the OP. — GMalc, Oct 05 '18 at 15:00
I would also change the range to only copy to the last row, not the whole columns. The Destination could always be changed to `PasteSpecial` for individuals who prefer `PasteSpecial` — GMalc, Oct 05 '18 at 15:04
So I tried GMalc's code, but i keep getting a run time error 1004 for application defined or object defined error for .Sheets("Invoice Totals").Range("A:R").Copy _ Destination:=ThisWorkbook.Sheets("Dump").Range(A2) — eenz, Oct 05 '18 at 22:54

Excel VBA: Subscript out of range code fix

1 Answers1