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

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.

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

Kate,

Try the following code:

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



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




"Kate" wrote in message
om...
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.

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

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



"Kate" wrote in message
om...
| 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.

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

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?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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

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.

--
Regards,
Tom Ogilvy



--
Regards,
Tom Ogilvy

"Katherina Holzhauser" wrote in message
...
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?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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

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



"Katherina Holzhauser" wrote in message
...
| 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?
|
| *** Sent via Developersdex http://www.developersdex.com ***
| Don't just participate in USENET...get rewarded for it!