Excel macro script to copy only formulas and not values from one sheet to another

[Kate]

I need a macro that will copy formulas in various cells from one
worksheet to another onto the same cell on the new worksheet. I want
to leave cells with values alone. Example (with formulas showing):
Sheet1:
A B C D
3 4 5 =sum(A1:C1)

Sheet2:
A B C D
7 6 5 18

Run Macro:
Sheet2 now reads:
A B C D
7 6 5 =sum(A1:C1)

I know how to find the formulas in Sheet1, but I can't figure out how
to copy them into the correct cells on Sheet2 leaving all the others
as is.

 

[Chip Pearson]

Kate,

Try the following code:

Dim Rng As Range
For Each Rng In
Worksheets("Sheet1").SpecialCells(xlCellTypeFormulas)
Worksheets("Sheet2").Range(Rng.Address).Formula = Rng.Formula
Next Rng



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[William]

Hi Kate

Sub test()
Application.ScreenUpdating = False
Dim ws As Worksheet, r As Range, c As Range
Set ws = ActiveSheet
With ws
Set r = .Cells.SpecialCells(xlCellTypeFormulas, 23)
End With
For Each c In r
c.Copy Sheets("Sheet2").Range(c.Address)
Next c
Application.ScreenUpdating = True
End Sub

--
XL2002
Regards

William

(e-mail address removed)

| I need a macro that will copy formulas in various cells from one
| worksheet to another onto the same cell on the new worksheet. I want
| to leave cells with values alone. Example (with formulas showing):
| Sheet1:
| A B C D
| 3 4 5 =sum(A1:C1)
|
| Sheet2:
| A B C D
| 7 6 5 18
|
| Run Macro:
| Sheet2 now reads:
| A B C D
| 7 6 5 =sum(A1:C1)
|
| I know how to find the formulas in Sheet1, but I can't figure out how
| to copy them into the correct cells on Sheet2 leaving all the others
| as is.

 

[Katherina Holzhauser]

Great - this one worked. One enhancement request: How do I prompt the
user to fill in the name of the sheet they want to copy from and the
name of the sheet they want to copy to?

 

[Tom Ogilvy]

Use an inputbox for each.

Sub test()
Application.ScreenUpdating = False
Dim srcSh As Worksheet, r As Range, c As Range
Dim destSh as Worksheet, src as string, dest as string
src = InputBox("Enter Source Sheet Name")
dest = Inputbox("Enter Destination Sheet Name")
if src = "" or dest = "" then
msgbox "missing sheet name"
exit sub
end if
On error resume Next
set srcSh = Worksheets(src)
set destSh = Worksheets(dest)
On Error goto 0
if srcSh is nothing or destSh is nothing then
msgbox "Non existent sheet"
exit sub
End if
With srcSh
Set r = .Cells.SpecialCells(xlCellTypeFormulas, 23)
End With
For Each c In r
c.Copy destSh.Range(c.Address)
Next c
Application.ScreenUpdating = True
End Sub

Untested, so there may be typos.

 

[William]

Hi Kate

Sub test()
Dim Message As String, Title As String, MyValue As String
Dim Message1 As String, Title1 As String, MyValue1 As String
Dim ws As Worksheet, r As Range, c As Range
On Error GoTo Err
Message = "Please enter the sheet name you want the cells copied from"
Title = "Copy From"
MyValue = InputBox(Message, Title)
With Sheets(MyValue)
Set r = .Cells.SpecialCells(xlCellTypeFormulas, 23)
End With
Message1 = "Please enter the sheet name you want the cells copied to"
Title1 = "Copy To"
MyValue1 = InputBox(Message1, Title1)
Application.ScreenUpdating = False
For Each c In r
c.Copy Sheets(MyValue1).Range(c.Address)
Next c
Application.ScreenUpdating = True
Exit Sub
Err:
MsgBox "Either a sheet name was not entered or the " & _
"sheet does not exist in this workbook"
End Sub

--
XL2002
Regards

William

(e-mail address removed)

| Great - this one worked. One enhancement request: How do I prompt the
| user to fill in the name of the sheet they want to copy from and the
| name of the sheet they want to copy to?
|
|
| Don't just participate in USENET...get rewarded for it!