[excel] Subscript out of range error in this Excel VBA script

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

This question is related to excel vba csv range

The answer is


This looks a little better than your previous version but get rid of that .Activate on that line and see if you still get that error.

Dim sh1 As Worksheet
set sh1 = Workbooks.Add(filenum(lngPosition) & ".csv")

Creates a worksheet object. Not until you create that object do you want to start working with it. Once you have that object you can do the following:

sh1.Range("A69").Paste
sh1.Range("A69").Select

The sh1. explicitely tells Excel which object you are saying to work with... otherwise if you start selecting other worksheets while this code is running you could wind up pasting data to the wrong place.


Private Sub CommandButton1_Click()

    Dim Data As Object, Employee As Object

    Application.ScreenUpdating = False

    Set Data = ThisWorkbook.Sheets("Data")

    Set Employee = ThisWorkbook.Sheets("Employee Names")

    Data.Range("AK1").Value = "Lookup"

    Data.Range("AK2:AK" & Data.Range("A1").End(xlDown).Row).Formula = "=VLOOKUP(E2,'Employee Names'!$A:$A,1,0)"

    Data.Range("AK2:AK" & Data.Range("A1").End(xlDown).Row).Value = Data.Range("AK2:AK" & Data.Range("A1").End(xlDown).Row).Value

    Data.Range("A1:AK" & Data.Range("A1").End(xlDown).Row).AutoFilter Field:=5, Criteria1:="<>"

    Data.Range("A1:AK" & Data.Range("A1").End(xlDown).Row).AutoFilter Field:=37, Criteria1:="#N/A"

    Application.DisplayAlerts = False

    Data.AutoFilter.Range.Offset(1, 0).Rows.SpecialCells(xlCellTypeVisible).Delete (xlShiftUp)

    Data.Range("AK:AK").Delete

    Data.AutoFilterMode = False

    'Selection.AutoFilter

    Data.Range("A1:AK" & Data.Range("A1").End(xlDown).Row).AutoFilter Field:=7, Criteria1:="="

    Data.Range("A1:AK" & Data.Range("A1").End(xlDown).Row).AutoFilter Field:=12, Criteria1:="<>"

    Worksheets("Data").Range("A1:AK" & Data.Range("A1").End(xlDown).Row).Copy

    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "DrfeeRequested"

    Set Dr = ThisWorkbook.Worksheets("DrfeeRequested")

    Dr.Range("A1").PasteSpecial Paste:=xlPasteValues

    Application.CutCopyMode = False

    Data.AutoFilterMode = False

    'DrfeeRequested.AutoFilterMode = False

    Selection.AutoFilter

    Data.Range("A1:AK" & Data.Range("A1").End(xlDown).Row).AutoFilter Field:=13, Criteria1:="<>"

    Data.Range("A1:AK" & Data.Range("A1").End(xlDown).Row).Copy

    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "RateLockfollowup"
    Set Ratefolup = ThisWorkbook.Worksheets("RateLockfollowup")

    Ratefolup.Range("A1").PasteSpecial Paste:=xlPasteValues

    Application.CutCopyMode = False

    Data.AutoFilterMode = False

    Selection.AutoFilter

    Data.Range("A1:AK" & Data.Range("A1").End(xlDown).Row).AutoFilter Field:=19, Criteria1:="="

    Data.Range("A1:AK" & Data.Range("A1").End(xlDown).Row).AutoFilter Field:=13, Criteria1:="<>"

    Data.Range("A1:AK" & Data.Range("A1").End(xlDown).Row).Copy

    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Lockedlefollowup"
    Set Lockfolup = ThisWorkbook.Worksheets("Lockedlefollowup")

    Lockfolup.Range("A1").PasteSpecial Paste:=xlPasteValues

    Application.CutCopyMode = False

    Data.AutoFilterMode = False

    Selection.AutoFilter

    Data.Range("A1:AK" & Data.Range("A1").End(xlDown).Row).AutoFilter Field:=19, Criteria1:="="

    Data.Range("A1:AK" & Data.Range("A1").End(xlDown).Row).Copy

    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Hoifollowup"

    Set Hoifolup = ThisWorkbook.Worksheets("Hoifollowup")

    Hoifolup.Range("A1").PasteSpecial Paste:=xlPasteValues

    Application.CutCopyMode = False

    Data.AutoFilterMode = False

    Selection.AutoFilter

    TodayDT = Format(Now())

    Weekdy = Weekday(Now())

    If Weekdy = 2 Then
       LastTwoDays = Now() - Weekday(Now(), 3)
    ElseIf Weekdy = 3 Then
       LastTwoDays = Now() - Weekday(Now(), 3)
    ElseIf Weekdy = 4 Then
       LastTwoDays = Now() - Weekday(Now(), 3)
    ElseIf Weekdy = 5 Then
       LastTwoDays = Now() - Weekday(Now(), 3)
    ElseIf Weekdy = 6 Then
       LastTwoDays = Now() - Weekday(Now(), 3)
    Else
       MsgBox "Today Satuarday OR Sunday Data is not Available"
    End If

    Data.Range("A1:AK" & Data.Range("A1").End(xlDown).Row).AutoFilter Field:=12, Criteria1:="="

    Data.Range("A1:AK" & Data.Range("A1").End(xlDown).Row).AutoFilter Field:=11, Criteria1:="<>"

    Data.Range("A1:AK" & Data.Range("A1").End(xlDown).Row).AutoFilter Field:=11, Criteria1:=" TodayDT", Operator:=xlAnd, Criteria2:="LastTwoDays"

    Data.Range("A1:AK" & Data.Range("A1").End(xlDown).Row).Copy

    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "DRfeefollowup"

    Set Drfreefolup = ThisWorkbook.Worksheets("DRfeefollowup")

    Drfreefolup.Range("A1").PasteSpecial Paste:=xlPasteValues

    Application.CutCopyMode = False

    Data.AutoFilterMode = False

    Data.Range("A1:AK" & Data.Range("A1").End(xlDown).Row).AutoFilter Field:=15, Criteria1:="yes"

    Data.Range("A1:AK" & Data.Range("A1").End(xlDown).Row).AutoFilter Field:=19, Criteria1:="x"

    Data.Range("A1:AK" & Data.Range("A1").End(xlDown).Row).AutoFilter Field:=12, Criteria1:="<>"

    Data.Range("A1:AK" & Data.Range("A1").End(xlDown).Row).AutoFilter Field:=13, Criteria1:="<>"

    'Data.Range("A1:AK" & Data.Range("A1").End(xlDown).Row).AutoFilter Field:=14, criterial:="<>"

    Data.Range("A1:AK" & Data.Range("A1").End(xlDown).Row).Copy

    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Drworkblefiles"

    Set Drworkblefiles = ThisWorkbook.Worksheets("Drworkblefiles")

    Drworkblefiles.Range("A1").PasteSpecial Paste:=xlPasteValues

    Application.CutCopyMode = False

    Data.Range("A1").AutoFilter

   End Sub

 Private Sub CommandButton2_Click()


    Sheets("Data").Range("A1:AJ" & Sheets("Data").Range("A1").End(xlDown).Row).Clear

    MsgBox "Please paste new data in data sheet"


End Sub

Examples related to excel

Python: Pandas pd.read_excel giving ImportError: Install xlrd >= 0.9.0 for Excel support Converting unix time into date-time via excel How to increment a letter N times per iteration and store in an array? 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data) How to import an Excel file into SQL Server? Copy filtered data to another sheet using VBA Better way to find last used row Could pandas use column as index? Check if a value is in an array or not with Excel VBA How to sort dates from Oldest to Newest in Excel?

Examples related to vba

Copy filtered data to another sheet using VBA Better way to find last used row Check if a value is in an array or not with Excel VBA Creating an Array from a Range in VBA Excel: macro to export worksheet as CSV file without leaving my current Excel sheet VBA: Convert Text to Number What's the difference between "end" and "exit sub" in VBA? Rename Excel Sheet with VBA Macro Extract Data from PDF and Add to Worksheet Quicker way to get all unique values of a column in VBA?

Examples related to csv

Pandas: ValueError: cannot convert float NaN to integer Export result set on Dbeaver to CSV Convert txt to csv python script How to import an Excel file into SQL Server? "CSV file does not exist" for a filename with embedded quotes Save Dataframe to csv directly to s3 Python Data-frame Object has no Attribute (unicode error) 'unicodeescape' codec can't decode bytes in position 2-3: truncated \UXXXXXXXX escape How to write to a CSV line by line? How to check encoding of a CSV file

Examples related to range

How does String substring work in Swift Creating an Array from a Range in VBA How to create range in Swift? Why is "1000000000000000 in range(1000000000000001)" so fast in Python 3? How to find integer array size in java How does one make random number between range for arc4random_uniform()? Skip over a value in the range function in python Excel Define a range based on a cell value How can I generate a random number in a certain range? Subscript out of range error in this Excel VBA script