复制/粘贴单元格和值-Java 学习之路

我想复制/粘贴所有工作表，包括单元格中的值/公式到另一个新工作簿 .

此代码只复制第一个ws，但不是所有其他的 . 我怎样才能确保所有ws都被复制和粘贴，而无需在vba代码中写入ws中的所有名称？

Sub CopyPaste()
Dim ws As Worksheet, wb As Workbook
Set ws = ActiveSheet
Set wb = Workbooks.Add(xlWBATWorksheet)
ws.Range("A1:G10").Copy
wb.Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteValues
wb.Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
End Sub

4 回答

所以我假设您将保存第二个工作簿以便命名？因此，只需在下面的路径中添加要保存的路径，现在它也会保留工作表名称 . 我不确定为什么你得到一个调试器错误它对我来说工作正常，尝试这个代码，看看你是否仍然得到它？

Sub newworkbook()
Dim WBN As workbook, WBC As workbook, WB As workbook
Dim WS As String
Dim SHT As Worksheet

Set WBN = Workbooks.Add

For Each WB In Application.Workbooks

If WB.Name <> WBN.Name Then
For Each SHT In WB.Worksheets
    SHT.Copy After:=WBN.Sheets(WBN.Worksheets.Count)
    WBN.Sheets(WBN.Worksheets.Count).Name = (SHT.Name) & " "
Next SHT
End If
Next WB
Application.DisplayAlerts = False
WBN.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Delete
WBN.Application.DisplayAlerts = True
ActiveWorkbook.SaveAs "C:\YOURPATH\timetable_v2.xls" 'change path to whatever
End Sub

回复于 2024-04-19T13:00:47+08:00

您可以尝试如下：

Sub CopyPaste()

    Dim aSheet As Worksheet
    Dim workbook As workbook
    Dim index As Integer

    Set workbook = Workbooks.Add(xlWBATWorksheet)

    For Each aSheet In Worksheets

        aSheet.Range("A1:G10").Copy

        workbook.Sheets(index).Range("A1").PasteSpecial Paste:=xlPasteFormulasAndNumberFormats

        index = index + 1

        Application.CutCopyMode = False

    Next aSheet


End Sub

回复于 2024-04-19T13:00:47+08:00

只是快速找你，这似乎做了这个工作：

信用：get digital help

Dim WBN As Workbook, WBC As Workbook, WB As Workbook
Dim WS As String
Dim SHT As Worksheet

Set WBN = Workbooks.Add
For Each WB In Application.Workbooks
If WB.Name <> WBN.Name Then
    For Each SHT In WB.Worksheets
        SHT.Copy After:=WBN.Sheets(WBN.Worksheets.Count)
        WBN.Sheets(WBN.Worksheets.Count).Name = Left(WB.Name, 30 - Len(SHT.Name)) & "-" & SHT.Name
    Next SHT
End If
Next WB
Application.DisplayAlerts = False
WBN.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Delete
WBN.Application.DisplayAlerts = True

回复于 2024-04-19T13:00:47+08:00

0
我刚刚删除了 WBN.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Delete 并且它工作正常

新工作簿保存为.xlsx文件，但当然我需要它作为.xlsm文件....当我刚刚将它添加到路径中时，它不起作用
```
ActiveWorkbook.SaveAs "U:\Excel\timetable_v2.xlsm"
```
回复于 2024-04-19T13:00:47+08:00

复制/粘贴单元格和值

4 回答

相关问题