时间戳

首页 > 解决方案 > 在 VBA 中使用当前区域会破坏我的 Excel

excel - 在 VBA 中使用当前区域会破坏我的 Excel

问题描述

Sub Alternate_Workbook_Reference()

'  Dim wsCopy As Worksheet
'  Dim lCopyLastRow As Long
  'The ThisWorkbook property can be used if the code is in the same
  'workbook as the copy or destination range.  In this case the code
  'is in the destination workbook.
  Call OpenWorkbook

  Workbooks("2020.revenue.bins.csv").Worksheets("2020.revenue.bins").Range("A1:C21").Copy _
    ThisWorkbook.Worksheets("2020 revenue bins").Range("A1")
  'CopyLastCColumn = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
'  Dim rng As Range, lastCell As Range
'  Set rng = Workbooks("2020.outcomes.csv").Worksheets("2020.outcomes").Range("A1").CurrentRegion       ' or Set rng = Sheet1.UsedRange.CurrentRegion
'  Set lastCell = rng(rng.Rows.Count, rng.Columns.Count)
  Workbooks("2020.outcomes.csv").Worksheets("2020.outcomes").Range("A1").CurrentRegion.Copy _
    ThisWorkbook.Worksheets("2020 outcomes").Range("A1") 

'  Workbooks("2020.outcomes.csv").Worksheets("2020.outcomes").Range("A1").CurrentRegion .Copy _
    ThisWorkbook.Worksheets("2020 outcomes").Range("A1")

  Call CloseWorkbook
End Sub

您好，我需要将 2020.outcomes.csv 中名为 2020.outcomes 的工作表中的数据复制到我当前工作簿的 2020 年结果表中。源 CSV 中的数据范围可能会有所不同，这就是我使用 CurrentRegion 的原因，但是一旦执行上述代码，我的工作簿就会崩溃，我无法再次打开它。我究竟做错了什么？打开和关闭工作簿是打开和关闭工作簿的简单代码提前致谢。

标签： excelvba

解决方案

也许这有任何帮助

Sub Alternate_Workbook_Reference()

    Dim wkb As Workbook

    Set wkb = Workbooks.Open("Complete Path and filename of 2020.outcomes.csv")
    wkb.Worksheets("2020.revenue.bins").Range("A1:C21").Copy _
        ThisWorkbook.Worksheets("2020 revenue bins").Range("A1")
    wkb.Close False

    Set wkb = Workbooks.Open("Complete Path and filename of 2020.outcomes.csv ")
    wkb.Worksheets("2020.outcomes").Range("A1").CurrentRegion.Copy _
        ThisWorkbook.Worksheets("2020 outcomes").Range("A1")
    wkb.Close False

End Sub

推荐阅读