Excel VBA：下标超出范围的代码修复

debugcn 发表于 Dev

一

我一直在行中收到下标超出范围错误

Sheets("Dump").Select

如何调整我的代码以消除错误？有没有办法调整它以删除 .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

GMalc

编辑（修复问题）试试这个...（在模拟数据上测试）。

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

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。