在此excel vba脚本中下标超出范围错误

问题描述

我想将数据从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

推荐答案

超出范围错误错误，因为它找不到该工作表。

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

也请...请不要使用 .Select / .Activate / Selection / ActiveCell 您可能想要查看中使用选择。

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脚本中下标超出范围错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！