Excel vba如何使用所有格式和页面设置复制工作表

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是全局定义的,工作簿是在另一个调用此代码的子类中创建的。

  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,它给了我适合并永远。

2 个解决方案

#1

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 :)

您正在使用Activeworkbook。当您添加新工作簿时,新工作簿将成为您的活动工作簿:)
The second problem is the DOT before .Sheets.Count in wb.Sheets(.Sheets.Count). why pick the count from the workbook you are copying?

第二个问题是wb.Sheets(.Sheets.Count)中的.Sheets.Count之前的DOT。为什么从你正在复制的工作簿中选择计数?

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

#2

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

#1