blocks|key|493505|text|更简单的方法是运行一个精确的副本，如下所示，将副本作为最后一个工作表放入|type|unstyled|depth|inlineStyleRanges|entityRanges|data|493506|Sub+Test()
Dim+ws1+As+Worksheet
Set+ws1+=+ThisWorkbook.Worksheets("Master")
ws1.Copy+ThisWorkbook.Sheets(Sheets.Count)
End+Sub|code-block|syntax|javascript|493507|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

It is simpler just to run an exact copy like below to put the copy in as the last sheet

<pre><code>Sub Test()
Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("Master")
ws1.Copy ThisWorkbook.Sheets(Sheets.Count)
End Sub
</code></pre>

blocks|key|493533|text|ThisWorkbook.Worksheets("Master").Sheet1.Cells.Copy+_
++++Destination:=newWorksheet.Cells|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|493534|上面的代码将复制单元格。如果你真的想复制整个工作表，那么我会选择@brettdj's+answer。|unstyled|offset|length|493535|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/8439771/119775^0|0|W|H|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|R|8|@]|9|@$G|S|H|T|1|U]]|A|$]]|$1|I|3|-4|5|F|7|V|8|@]|9|@]|A|$]]]|J|$K|$5|L|M|N|A|$O|P]]]]

<pre><code>ThisWorkbook.Worksheets("Master").Sheet1.Cells.Copy _
 Destination:=newWorksheet.Cells
</code></pre>

The above will copy the cells. If you really want to duplicate the entire sheet, then I'd go with <a href="https://stackoverflow.com/a/8439771/119775">@brettdj's answer</a>.

blocks|key|493549|text|'Make+the+excel+file+that+runs+the+software+the+active+workbook
ThisWorkbook.Activate

'The+first+sheet+used+as+a+temporary+place+to+hold+the+data+
ThisWorkbook.Worksheets(1).Cells.Copy

'Create+a+new+Excel+workbook
Dim+NewCaseFile+As+Workbook
Dim+strFileName+As+String

Set+NewCaseFile+=+Workbooks.Add
With+NewCaseFile
++++Sheets(1).Select
++++Cells(1,+1).Select
End+With

ActiveSheet.Paste|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|493550|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>'Make the excel file that runs the software the active workbook
ThisWorkbook.Activate

'The first sheet used as a temporary place to hold the data 
ThisWorkbook.Worksheets(1).Cells.Copy

'Create a new Excel workbook
Dim NewCaseFile As Workbook
Dim strFileName As String

Set NewCaseFile = Workbooks.Add
With NewCaseFile
 Sheets(1).Select
 Cells(1, 1).Select
End With

ActiveSheet.Paste
</code></pre>

blocks|key|493569|text|'+Assume+that+the+code+name+the+worksheet+is+Sheet1

'+Copy+the+sheet+using+code+name+and+put+in+the+end.
'+Note:+Using+the+code+name+lets+the+user+rename+the+worksheet+without+breaking+the+VBA+code
Sheet1.Copy+After:=Sheets(Sheets.Count)

'+Rename+the+copied+sheet+keeping+the+same+name+and+appending+a+string+"+copied"
ActiveSheet.Name+=+Sheet1.Name+&+"+copied"|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|493570|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>' Assume that the code name the worksheet is Sheet1

' Copy the sheet using code name and put in the end.
' Note: Using the code name lets the user rename the worksheet without breaking the VBA code
Sheet1.Copy After:=Sheets(Sheets.Count)

' Rename the copied sheet keeping the same name and appending a string " copied"
ActiveSheet.Name = Sheet1.Name &amp; " copied"
</code></pre>

blocks|key|709414|text|我真的很喜欢@brettdj的代码，但后来我发现，当我添加额外的代码来编辑副本时，它会覆盖我原来的工作表。我已经调整了他的答案，以便针对ws1的进一步代码将影响新的工作表，而不是原始的工作表。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|709415|Sub+Test()
++++Dim+ws1+as+Worksheet
++++ThisWorkbook.Worksheets("Master").Copy
++++Set+ws1+=+ThisWorkbook.Worksheets("Master+(2)")
End+Sub|code-block|syntax|javascript|709416|entityMap^0|1W|3|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

I really liked @brettdj's code, but then I found that when I added additional code to edit the copy, it overwrote my original sheet instead. I've tweaked his answer so that further code pointed at <code>ws1</code> will affect the new sheet rather than the original.

<pre><code>Sub Test()
 Dim ws1 as Worksheet
 ThisWorkbook.Worksheets("Master").Copy
 Set ws1 = ThisWorkbook.Worksheets("Master (2)")
End Sub
</code></pre>

blocks|key|493723|text|如果有人像我一样，有一个预估工作簿，其中包含默认数量的可见定价表、摘要和大量充满敏感数据的隐藏和“受保护”工作表，但可能需要创建额外的可见工作表才能达到适当的价格，我有上述响应的变体，基于受保护的隐藏“主控”创建上述可见工作表。我将@/jean-fran%25c3%25a7ois-corbett和@thanos+a提供的代码与简单的VBA结合使用，如下所示。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|493724|子sbInsertWorksheetAfter()|493725|++++'This+adds+a+new+visible+worksheet+after+the+last+visible+worksheet

++++ThisWorkbook.Sheets.Add+After:=Worksheets(Worksheets.Count)

++++'This+copies+the+content+of+the+HIDDEN+"Master"+worksheet+to+the+new+VISIBLE+ActiveSheet+just+created

++++ThisWorkbook.Sheets("Master").Cells.Copy+_
++++++++Destination:=ActiveSheet.Cells

++++'This+gives+the+the+new+ActiveSheet+a+default+name

++++With+ActiveSheet
++++++++.Name+=+Sheet12.Name+&+"+copied"
++++End+With

++++'This+changes+the+name+of+the+ActiveSheet+to+the+user's+preference

++++Dim+sheetname+As+String

++++With+ActiveSheet
++++++++sheetname+=+InputBox("Enter+name+of+this+Worksheet")
++++++++.Name+=+sheetname
++++End+With|code-block|syntax|javascript|493726|结束子对象|493727|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

If anyone has, like I do, an Estimating workbook with a default number of visible pricing sheets, a Summary and a larger number of hidden and 'protected' worksheets full of sensitive data but may need to create additional visible worksheets to arrive at a proper price, I have variant of the above responses that creates the said visible worksheets based on a protected hidden "Master". I have used the code provided by @/jean-fran%c3%a7ois-corbett and @thanos-a in combination with simple VBA as shown below.

Sub sbInsertWorksheetAfter()

<pre><code> 'This adds a new visible worksheet after the last visible worksheet

 ThisWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)

 'This copies the content of the HIDDEN "Master" worksheet to the new VISIBLE ActiveSheet just created

 ThisWorkbook.Sheets("Master").Cells.Copy _
 Destination:=ActiveSheet.Cells

 'This gives the the new ActiveSheet a default name

 With ActiveSheet
 .Name = Sheet12.Name &amp; " copied"
 End With

 'This changes the name of the ActiveSheet to the user's preference

 Dim sheetname As String

 With ActiveSheet
 sheetname = InputBox("Enter name of this Worksheet")
 .Name = sheetname
 End With
</code></pre>

End Sub

I have found similar questions that deal with copying an entire worksheet in one workbook and pasting it to another workbook, but I am interested in simply copying an entire worksheet and pasting it to a new worksheet -- in the same workbook. 

I'm in the process of converting a 2003 .xls file to 2010 .xlsm and the old method used for copying and pasting between worksheets doesn't paste with the correct row heights. My initial workaround was to loop through each row and grab the row heights from the worksheet I am copying from, then loop through and insert those values for the row heights in the worksheet I am pasting to, but the problem with this approach is that the sheet contains buttons which generate new rows which changes the row numbering and the format of the sheet is such that all rows cannot just be one width. 

What I would really like to be able to do is just simply copy the entire worksheet and paste it. Here is the code from the 2003 version:

<pre><code>ThisWorkbook.Worksheets("Master").Cells.Copy
newWorksheet.Paste
</code></pre>

I'm surprised that converting to .xlsm is causing this to break now. Any suggestions or ideas would be great.

Copy an entire worksheet to a new worksheet in Excel 2010

我发现过类似的问题，涉及复制一个工作簿中的整个工作表并将其粘贴到另一个工作簿中，但我感兴趣的是简单地复制整个工作表并将其粘贴到新工作表中--在同一个工作簿中。我正在将2003年的.xls文件转换为2010年的.xlsm，但用于在工作表之间复制和粘贴的旧方法无法以正确的行高粘贴。我最初的解决方法是循环遍历每一行，并从我正...

问将整个工作表复制到Excel 2010中的新工作表
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将整个工作表复制到Excel 2010中的新工作表EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将整个工作表复制到Excel 2010中的新工作表
EN