☰

excel - 如何將所有工作表從一個工作簿複製到另一個工作簿

tag-icon • tag-icon vba microsoft-excel-2013
excel - 如何將所有工作表從一個工作簿複製到另一個工作簿

我有一本名為「測試」的工作簿。我想複製名為“估計”的工作簿中的所有工作表進行測試。我不知道如何建立可以複製到“測試”的“估計”工作表列表。有沒有辦法在VBA中做到這一點？

答案1

考慮：

Sub dural()
   Dim b1 As Workbook, b2 As Workbook
   Dim sh As Worksheet

   Workbooks.Open Filename:="C:\TestFolder\test.xls"
   Set b1 = ActiveWorkbook
   Workbooks.Open Filename:="C:\TestFolder\estimate.xls"
   Set b2 = ActiveWorkbook

   For Each sh In b2.Sheets
      sh.Copy after:=b1.Sheets(b1.Sheets.Count)
   Next sh
End Sub

答案2

這將列出它們

Sub list()
Dim wbEstimate As Workbook
Set wbEstimate = ThisWorkbook
Dim wsList As Worksheet
Dim numSheets As Integer
numSheets = wbEstimate.Sheets.Count
Worksheets.Add(After:=Worksheets(numSheets)).Name = "list"
Set wsList = Worksheets("list")
Dim i As Integer
i = 1

For Each Sheet In wbEstimate.Worksheets
    wsList.Cells(i, 1) = Sheet.Name
    i = i + 1
Next
End Sub

這應該複製它們如果它們都打開

Sub list()
Dim wbEstimate As Workbook
Set wbEstimate = ThisWorkbook
Dim wbTest As Workbook
Set wbTest = Workbooks("test.xlsx")

For Each Sheet In wbEstimate.Sheets
    Sheet.Copy after:=wbTest.Sheets(wbTest.Sheets.Count)
Next
End Sub

如果您需要在影印表格之前打開測試版，請這樣做 -

Sub list()
Dim wbEstimate As Workbook
Set wbEstimate = ThisWorkbook
Dim wbTest As Workbook
Set wbTest = Workbooks.Open("C:\Users\path\to\test.xlsx")

For Each Sheet In wbEstimate.Sheets
    Sheet.Copy after:=wbTest.Sheets(wbTest.Sheets.Count)
Next
End Sub

相關內容