Excel中的宏复制工作表(通过引用每个单元格)(Macro in Excel to Copy a Worksheet (by referencing every cell))

Office IT屋
问 题

I've found lots of examples for Copying Worksheets in VBA, or replacing formulas with values etc. What I want to do is copy an entire worksheet, but on the new worksheet, every cell refers back to its original. So in cell A1 of the new worksheet, it would simply have the formula "='Sheet1'!A1"

Is there an easy way to do this? Thanks

P.S. I need it to be a Macro, as I need to be able to run it on specific sheets, to copy all the cells from that sheet into a new one, not always from "Sheet1"

解决方案

If you want to avoid the clipboard may I suggest R1C1 formula format:

Sub fillsheet()
Dim ows As Worksheet
Dim tws As Worksheet
Dim rng As Range

Set ows = Worksheets("Sheet1")
Set tws = Worksheets("Sheet2")

Set rng = ows.UsedRange

tws.Range(rng.Address()).FormulaR1C1 = "='" & ows.Name & "'!RC"
End Sub

本文地址:IT屋 » Macro in Excel to Copy a Worksheet (by referencing every cell)

问 题

我已经发现了许多在VBA中复制工作表的示例,或者用值等替换公式。
我想做的是复制整个工作表,但是在新的工作表中,每个单元格都指回原版的。
所以在新的工作表的单元格A1中,它只会有公式“='Sheet1'!A1”



有没有一个简单的方法来做到这一点?
谢谢



我需要它是一个宏,因为我需要能够在特定的工作表上运行它,将该工作表中的所有单元格复制到一个新的单元格,而不是始终从“Sheet1”


解决方案

如果你想避免剪贴板,我可以建议R1C1公式格式:



  Sub fillsheet()
Dim ows As Worksheet
Dim tws As Worksheet
Dim rng As Range

设置ows = Worksheets(“Sheet1”)
设置tws =工作表(“Sheet2”)

设置rng = ows.UsedRange

tws.Range(rng.Address())。FormulaR1C1 =“='” &安培; ows.Name& “'!RC”
End Sub

本文地址:IT屋 » Excel中的宏复制工作表(通过引用每个单元格)