Copying Sheet with Formulas

[Ashok Kumar]

Hi all

I am trying to write a macro which will copy a worksheet from one Workbook to another using the Worksheets collections Copy method. The source Sheet has formulas which refer to other sheets cells in the Source workbook. The Target workbook also has the same set of worksheets (with the same name) except the one that I am copying. After copying, what I noticed is that the copied sheet still refers to the Source workbook's sheets in all formulas rather than the one present in the current workbook. How do I make it refer to the current workbook and not the source workbook

for e.g.

Source work book (abc.xls) has Sheet1, Sheet2, Sheet3. There is a formula in the Sheet3 which is "=Sheet1!C3*1.12". The target work book (xyz.xls) has Sheet1 and Sheet2. When I open both the workbooks and select the Sheet3 of the abc.xls and say copy to the xyz.xls, the formula in the sheet3 of the xyz.xls becomes "=[abc.xls]Sheet1!C3*1.12". What do I need to do if it has to remain like "=Sheet1!C3*1.12" and not refer to the source file.

 

[yogendra joshi]

Try this,

1. Press F2 in the cell you want to copy
2. Copy the entire formula (Select and Ctrl + C)
3. Press Enter
4. Now go the the workbook where you want to paste (or press Ctrl + F6)
5. Go to the cell you want to paste and Paste.

It works perfectly.

 

[Ashok Kumar]

Thank you for your suggestion. But unfortunately, You have not understood the problem I guess. I want the entire sheet to copied and not just one formula in one cell. And the entire sheet has so many formulas. Also, I want write a macro for this purpose and a manual process.

 

[Dave Peterson]

When I do this manually, I like to convert my formulas to strings first, copy,
then convert them back to formulas.

Option Explicit
Sub testme01()

Dim abcWks As Worksheet
Dim xyzWks As Worksheet
Dim xyzWkbk As Workbook

Set abcWks = Workbooks("abc.xls").Worksheets("sheet3")
Set xyzWkbk = Workbooks("xyz.xls")

With abcWks
.UsedRange.Cells.Replace what:="=", replacement:="$$$$$", _
lookat:=xlPart, MatchCase:=False
.Copy _
Before:=xyzWkbk.Worksheets(1)
Set xyzWks = ActiveSheet
.UsedRange.Cells.Replace what:="$$$$$", replacement:="=", _
lookat:=xlPart, MatchCase:=False
End With
With xyzWks
.UsedRange.Cells.Replace what:="$$$$$", replacement:="=", _
lookat:=xlPart, MatchCase:=False
End With

End Sub

But if you look under Edit|Links, you'll see an option to change those links.
And you can point to the new workbook.

Sub testme02()

Dim abcWks As Worksheet
Dim xyzWks As Worksheet
Dim xyzWkbk As Workbook

Set abcWks = Workbooks("abc.xls").Worksheets("sheet3")
Set xyzWkbk = Workbooks("xyz.xls")

abcWks.Copy _
Before:=xyzWkbk.Worksheets(1)

xyzWkbk.ChangeLink Name:=abcWks.Parent.Name, _
NewName:=xyzWkbk.Name, Type:=xlExcelLinks

End Sub

And both these techniques worked manually and via a macro.

 

[Ashok Kumar]

Hi Dave

Thanks a lot. Both techniques worked for me also. Anyways, I am sticking with the second one, which looks much cleaner

Thanks a lot again

Ashok Kumar