copy column formulas

[lsaari]

I want to be able to copy formulas in one column to another column without copying the data that is there. I realize I can drag cells, but there are so many I don't want to do that. I have tried copying and pasting "formulas", but the numbers from my original column paste over my numbers in the new column.

 

[mth0012]

When pasting the formula, are you keeping the data column the same relative position to the formula column?
Can you post an image of your sheet?

 

[lsaari]

I am trying to copy column C to column E and G - but I only want the formulas to copy. I need the data to remain what it is.

 

[Joe4]

Do you want to overwrite what is in columns E and G?
Or do you want to only copy it to cells in columns E and G that are blank?

What about when column C is blank?
Do the blank columns contain formulas, or nothing at all?

 

[lsaari]

I do not want to overwrite the actual numbers - except where a formula would be appropriate. (the sub total lines and/or the total lines). I just want to only copy the formulas from one column to the next. I have reports that download to excel, but there are no formulas. I want formulas and the original numbers. So I set up the formulas in the first column and want them in all the columns. i don't want to have to click and drag the cell across the page to get it into the rest of the columns. Am I making sense. I don't know how else to explain it.

See my screen shot I have attached. I made the 485077.84 in column c, line 15 a formula. but the 16930.92 and the 502008.76next to it is not yet a formula, it is just a value. I was imagining a function that would allow me to click all of column c and copy only the formulas to column E. so then the 16930.92 or the 502008.76 would not just be values anymore, but would calculate. If I have to change a number I want the totals to change with it.

Again, I realize i could make a formula and then drag it across but sometimes it is easier to work with one column at a time. the blank columns could be deleted. they just sent to excel that way from my accounting package.

 

[Joe4]

So, is it only the "Total" rows that you want to do this too?

 

[lsaari]

yes.

 

[Joe4]

See if this VBA code does what you want:

Sub MyCopyTotals()

    Dim lr As Long, r As Long
    
    Application.ScreenUpdating = False
    
'   Find last row with data in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows starting with row 12
    For r = 12 To lr
'       See if column A starts with "Total..."
        If Left(Cells(r, "A"), 5) = "Total" Then
'           Copy formula in column C to columns E and G
            Cells(r, "C").Copy Cells(r, "E")
            Cells(r, "C").Copy Cells(r, "G")
        End If
    Next r

    Application.ScreenUpdating = True
    
End Sub

 

[lsaari]

Thank you. I will give it a try.