Excel vba如何复制表格与所有格式&页面设置 [英] Excel vba how to copy sheet with all formatting & page setup

问题描述

我已经看到了很多例子来制作完整的工作表副本，但是没有一个是为我工作的。在我的情况下，工作表必须进入一个新的工作簿。在我的实际代码中，wb被定义为全局，并且工作簿是在另一个被称为这个的子类中创建的。

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.

  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

没有工作。

同样

  ActiveWorkbook.Sheets("Sample Attendance").Copy After:=wb.Sheets(1)
  Set newtab = wb.Sheets("Sample Attendance")
  newtab.Name = tabname

这两种方法都在Copy语句之后返回。

both methods return after the Copy statement.

我已经适度地成功了：

  Set newtab = wb.Worksheets.Add
  newtab.Name = tabname
  Set Attendance = ThisWorkbook.Sheets("Sample Attendance")
  Attendance.Range("A:BB").Copy Destination:=newtab.Cells(1, 1)

哪些工作。但是，我必须复制所有的给我的配置并且永远使用的PageSetup。

which works. But then I have to copy all of the PageSetup across which is giving me fits and takes forever.

推荐答案

我看到两个问题您的第一段代码...

I see two problems with your 1st piece of code...

1. 您正在使用 Activeworkbook 。当您添加新的工作簿时，新的工作簿将成为您的活动工作簿：）

1. You are using Activeworkbook. When you add a new workbook, the new workbook becomes your active workbook :)

第二个问题是。在 wb.Sheets（.Sheets.Count）中计数。为什么要从您正在复制的工作簿中选择计数？

The second problem is the DOT before .Sheets.Count in wb.Sheets(.Sheets.Count). why pick the count from the workbook you are copying?

尝试这个

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

这篇关于Excel vba如何复制表格与所有格式&页面设置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！