Excel vba 如何使用所有格式和页面设置复制工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18794284/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel vba how to copy sheet with all formatting & page setup
提问by marcp
I've seen quite a few examples for making a full copy of a worksheet but none of them are working for me. In my case the sheet has to go into a new workbook. In my actual code wb is defined global and the workbook is created in another sub that called this one.
我已经看到了很多制作工作表完整副本的示例，但没有一个对我有用。在我的情况下，工作表必须进入一个新的工作簿。在我的实际代码中 wb 是全局定义的，工作簿是在另一个名为 this 的子程序中创建的。
Dim wb As Workbook
Set wb = Workbooks.Add()
Dim newtab as Worksheet
With ActiveWorkbook
.Sheets("Sample Attendance").Copy After:=wb.Sheets(.Sheets.Count)
Set newtab = wb.Sheets(wb.Sheets.Count - 1)
End With
didn't work.
没有用。
Likewise
同样地
ActiveWorkbook.Sheets("Sample Attendance").Copy After:=wb.Sheets(1)
Set newtab = wb.Sheets("Sample Attendance")
newtab.Name = tabname
both methods return after the Copy statement.
这两种方法都在 Copy 语句之后返回。
I've been moderately successful with this:
我在这方面取得了一定的成功：
Set newtab = wb.Worksheets.Add
newtab.Name = tabname
Set Attendance = ThisWorkbook.Sheets("Sample Attendance")
Attendance.Range("A:BB").Copy Destination:=newtab.Cells(1, 1)
which works. But then I have to copy all of the PageSetup across which is giving me fits and takes forever.
哪个有效。但随后我必须复制所有的 PageSetup，这让我永远无法适应。
回答by Siddharth Rout
I see two problems with your 1st piece of code...
我看到你的第一段代码有两个问题......
You are using
Activeworkbook. When you add a new workbook, the new workbook becomes your active workbook :)The second problem is the DOT before
.Sheets.Countinwb.Sheets(.Sheets.Count). why pick the count from the workbook you are copying?
您正在使用
Activeworkbook. 添加新工作簿时，新工作簿将成为您的活动工作簿 :)第二个问题是
.Sheets.Countin之前的DOTwb.Sheets(.Sheets.Count)。为什么要从您正在复制的工作簿中选择计数？
Try this
尝试这个
Sub Sample()
Dim thiswb As Workbook, wb As Workbook
Dim newtab As Worksheet
Set thiswb = ThisWorkbook
Set wb = Workbooks.Add()
With thiswb
.Sheets("Sample Attendance").Copy After:=wb.Sheets(wb.Sheets.Count)
Set newtab = wb.Sheets(wb.Sheets.Count - 1)
End With
End Sub
回答by Gary's Student
Give this a shot:
试一试：
Sub SheetCopier()
Dim OriginalWB As Workbook
Dim NewWB As Workbook
Set OriginalWB = ActiveWorkbook
Workbooks.Add
Set NewWB = ActiveWorkbook
OriginalWB.Sheets("qwerty").Copy Before:=NewWB.Sheets(1)
End Sub
