Copy only formulas??

  • Thread starter Thread starter Andre Croteau
  • Start date Start date
A

Andre Croteau

Hi !

I am at work now, and I hope this procedure can be done before I leave
tonight

I have a column A full of values that were copied/PastedSpecialValues from
other spreadsheets (no other choice than to copy/pasteSpecialValue)
In this example, cell A5 is the value of the sum A1:A4...but it is a VALUE
In colum B, I have a series of formulas (but no data) that I need to copy in
column A, without changing the actual valid data in column A
The result is that I would like to have in cell A5 =sum(A1:A4)

I have tried combinations of PasteSpecialFormulaAdd and others, but does not
get the results I want.
Is there a way to do this on a larger scale, as I have many spreadsheets,
and different months to do.

A B
1
2 2
3 4
4 5
5 11 =Sum(B1:B4)
6
7 8
8 9
9 17 =sum(B7:B9)


Thanks for all your help, and wish all of you the best for the upcoming
Holidays.

André
 
This worked for me:

Select column B,
Then <Edit> <Replace>
In the replace what box, enter "="(equal sign - no quotes),
In the replace with box, enter "zz" (no quotes),
Click "Replace All".

While column B is still selected, right click in it and choose "Copy",

Now select column A,
Right click in it and choose "PasteSpecial",
Then click "Values" *AND* then click "Skip Blanks",
Then <OK>

Next, while column A is still selected,
<Edit> <Replace>
In the replace what box, enter "zz" (no quotes),
In the replace with box, enter ),"=" (equal sign - no quotes),
Click "Replace All",

And you should now have all your formulas in column A where you want them,
and still retain the column A data values.

You could now go back to column B and return the equal sign to remake them
formulas, or delete them ... as you wish.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
 
Couple of Routines that will do as you wish, each time taking all the formulas
in Col B and pasting them into Col A. Assumes that as per your example, all the
cells between the formulas in Col B are really blank:-

Sub CopyFormulas1()

Dim r As Long
Dim lrow As Long

Application.ScreenUpdating = False

lrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
For r = 1 To lrow
With Cells(r, 2)
If .Value <> "" Then
.Copy Cells(r, 1)
End If
End With
Next r

Application.ScreenUpdating = True

End Sub

----------------------------------------------------------------------

Sub CopyFormulas2()

Dim r As Long
Dim lrow As Long

Application.ScreenUpdating = False

lrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

Range(Cells(1, 2), Cells(lrow, 2)).Copy
Range("A1").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=True, Transpose:=False

Application.ScreenUpdating = True

End Sub

---------------------------------------------------------------------

If however, the cells between your formulas in Col B are not really blank, but
also contain data, then use the following:-

Sub CopyFormulas3()

Dim r As Long
Dim lrow As Long

Application.ScreenUpdating = False

lrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
For r = 1 To lrow
With Cells(r, 2)
If .HasFormula = True Then
.Copy Cells(r, 1)
End If
End With
Next r

Application.ScreenUpdating = True

End Sub

If you are not sure how to use a macro, or need to change ranges etc then just
post back and ask.
 
Forgot a couple of things like taking off the cutcopy mode.

Have also added a 4th routine that will ask you what column you want to copy and
what column you want to paste it to. Hopefully you want to do everything from
the first row :-)

Sub CopyFormulas1()

Dim r As Long
Dim lrow As Long

Application.ScreenUpdating = False

lrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
For r = 1 To lrow
With Cells(r, 2)
If .Value <> "" Then
.Copy Cells(r, 1)
End If
End With
Next r

Application.ScreenUpdating = True

Range("A1").Select

End Sub

-------------------------------------------------------------------

Sub CopyFormulas2()

Dim r As Long
Dim lrow As Long

Application.ScreenUpdating = False

lrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

Range(Cells(1, 2), Cells(lrow, 2)).Copy
Range("A1").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=True, Transpose:=False

Application.CutCopyMode = False
Application.ScreenUpdating = True

Range("A1").Select

End Sub

-------------------------------------------------------------------

Sub CopyFormulas3()

Dim r As Long
Dim lrow As Long

Application.ScreenUpdating = False

lrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
For r = 1 To lrow
With Cells(r, 2)
If .HasFormula = True Then
.Copy Cells(r, 1)
End If
End With
Next r

Application.ScreenUpdating = True

Range("A1").Select

End Sub

-------------------------------------------------------------------

Sub CopyFormulasAskMe()

Dim r As Long
Dim f As String
Dim d As String
Dim lrow As Long

f = InputBox("What Column are the formulas in that you wish to Copy?")
d = InputBox("What Column do you want to paste them to?")

Application.ScreenUpdating = False

lrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
For r = 1 To lrow
With Cells(r, f)
If .HasFormula = True Then
.Copy Cells(r, d)
End If
End With
Next r

Application.ScreenUpdating = True

Range("A1").Select

End Sub
 
Hello Ragdyer,

Thanks for your reply.

However, in your method, the sum formula was copied exactly to column A.
The formula shown in cell A5 is shown as Sum(B1:B4).

I did however used your tip of clicking on the skip blanks,.
So in using Copy/PasteSpecialFormulas+SkipBlanks , it works perfectly

Thanks!

André
 
Hi Ken,

I have tried your macros, and they work like a charm.

One of these days, I'll sit down and seriously learn more about VBA coding.

Thank you

André
 
So will I :-)

My pleasure though - Thanks for the feedback, and have a great Xmas and New
year.
 
Back
Top