我最后一次需要幫助，下面的代碼作業正常；它將值（A1）從陣列中的作業表復制到組織檔案中新創建的作業表。我想在這里做的最后一個修改是，在這個 NOT_ORG 檔案中，我想復制值的范圍，而不是 1 個值。此范圍始終從 A7 開始，但列數和行數可能會發生變化。我想動態復制此范圍并粘貼到新創建的作業表中的范圍（a1）中。我知道我應該計算 lastRow 和 lastCol，但不確定把這段代碼放在哪里，以及如何修改最后一個復制行來實作這個結果。
標記@faneduru，因為他最初幫助了我。
Sub Test1()
Dim lastRow As Long
Dim WshtNames As Variant
Dim WshtNameCrnt As Variant
Dim WB1 As Workbook
Dim WB2 As Workbook
Set WB1 = ActiveWorkbook
Set WB2 = Workbooks.Open("C:\NOT_ORG.xlsx")
WshtNames = Array("2", "3")
For Each WshtNameCrnt In WshtNames
WB1.Sheets.Add.Name = WshtNameCrnt & "_new"
WB2.Worksheets(WshtNameCrnt).Range("A1").Copy ActiveSheet.Range("A1")
Next WshtNameCrnt
End Sub
提前致謝。電磁場
uj5u.com熱心網友回復：
請測驗下一個代碼：
Sub Test1()
Dim lastRow As Long, lastCol As Long, WshtNames, WshtNameCrnt
Dim WB1 As Workbook, WB2 As Workbook, ws As Worksheet
Set WB1 = ActiveWorkbook
Set WB2 = Workbooks.Open("C:\NOT_ORG.xlsx")
WshtNames = Array("2", "3")
For Each WshtNameCrnt In WshtNames
WB1.Sheets.Add.Name = WshtNameCrnt & "_new"
Set ws = WB2.Worksheets(WshtNameCrnt)
lastRow = ws.Range("A" & ws.rows.count).End(xlUp).row
lastCol = ws.cells(7, ws.Columns.count).End(xlToLeft).Column
ws.Range(ws.Range("A" & 7), ws.cells(lastRow, lastCol)).Copy ActiveSheet.Range("A1")
Next WshtNameCrnt
End Sub
還有一個更快的版本，使用陣列：
Sub Test1Array()
Dim lastRow As Long, lastCol As Long, WshtNames, WshtNameCrnt
Dim WB1 As Workbook, WB2 As Workbook, ws As Worksheet, arr
Set WB1 = ActiveWorkbook
Set WB2 = Workbooks.Open("C:\NOT_ORG.xlsx")
WshtNames = Array("2", "3")
For Each WshtNameCrnt In WshtNames
WB1.Sheets.Add.Name = WshtNameCrnt & "_new"
Set ws = WB2.Worksheets(WshtNameCrnt)
lastRow = ws.Range("A" & ws.rows.count).End(xlUp).row
lastCol = ws.cells(7, ws.Columns.count).End(xlToLeft).Column
arr = ws.Range(ws.Range("A" & 7), ws.cells(lastRow, lastCol)).value
ActiveSheet.Range("A1").Resize(UBound(arr), UBound(arr, 2)).value = arr
Next WshtNameCrnt
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/346666.html
標籤：vba