这个excel vba脚本中的下标超出范围错误 [英] Subscript out of range error in this excel vba script

问题描述

我想将数据从csv文件复制到excel工作表中。有11个csv文件。
到目前为止我已经有了（这是以前发布的一个修改版本）：

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

请帮我解决这个问题。
如果还有其他提示如何改进这个代码，请让我知道

Please help me fix this. And also, if there are any other tips on how to improve this code please let me know

推荐答案

设置sh1 =工作表（filenum（lngPosition））。激活

Set sh1 = Worksheets(filenum(lngPosition)).Activate

您正在获得下标超出范围错误错误，因为它找不到工作表。

You are getting Subscript out of range error error becuase it cannot find that Worksheet.

另请请...请不要使用。选择/。激活/选择/ ActiveCell 您可能希望看到如何避免使用Excel中的选择VBA宏。

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.

这篇关于这个excel vba脚本中的下标超出范围错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！