Subscript out of range error in this Excel VBA script
218,832
Set sh1 = Worksheets(filenum(lngPosition)).Activate
You are getting Subscript out of range error
error becuase it cannot find that Worksheet.
Also please... please... please do not use .Select/.Activate/Selection/ActiveCell
You might want to see How to Avoid using Select in Excel VBA Macros.
Author by
user2883071
Updated on January 17, 2020Comments
-
user2883071 over 4 years
I would like to copy data from a CSV file into an Excel worksheet. There are 11 .csv files. So far I have this (it is a modified version from a previous post):
Sub importData() Dim filenum(0 To 10) As Long filenum(0) = 052 filenum(1) = 060 filenum(2) = 064 filenum(3) = 068 filenum(4) = 070 filenum(5) = 072 filenum(6) = 074 filenum(7) = 076 filenum(8) = 178 filenum(9) = 180 filenum(10) = 182 Dim sh1 As Worksheet On Error GoTo my_handler For lngPosition = LBound(filenum) To UBound(filenum) 'Windows(filenum(lngPosition) & ".csv").Activate Workbooks.Add(filenum(lngPosition) & ".csv").Activate Range("A1").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Copy Windows("30_graphs_w_Macro.xlsm").Activate Set sh1 = Worksheets(filenum(lngPosition)).Activate Range("A69").Paste Range("A69").Select Next lngPositionlngPositionlngPosition my_handler: MsgBox "All done." Exit Sub End Sub
This code gives me a subscript out of range error on the line:
Set sh1 = Worksheets(filenum(lngPosition)).Activate
-
Pretasoc over 5 yearsPlease don't just post code. Instead explain why your code solves the ops question.