copy paste as value except total rows

Vcoppens

Board Regular
Joined
Apr 16, 2012
Messages
90
Hi, I am not sure if this is possible.

I have an excel spreadsheet with lots of formulas - I call it my worksheet.

To be presented to mgmnt, the content needs to be pasted as a numbers value only. This I know how to do.

The "Total" rows (meaning subtotal and grandtotal rows) need to show the formulas in the final spreadsheet.

Is there an easy way to do this?

Right now I do copy - paste value and overwrite the "Totals" row as a copy - paste formula.

I am not very good with macros.

Thank you very much !
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
I would do it exactly as you said.
Another option would be to create a copy of the original sheet (w/ the formulas) & change all of the formulas to =Sheet1!A1 (A2,A3 & so forth), then hide Sheet1.
They get their numbers, the formulas on what they see are meaningless, your formulas are protected (but still there) & you can protect the whole thing, so that they can't break it (or unhide Sheet1).
 
Upvote 0
Guess using "Show Formula" function in the formula tab will help you.
 
Upvote 0
@Vcoppens
Make a sheet called "mgmnt" then use this code


Code:
Sub MM1()
Dim lr As Long
ActiveSheet.UsedRange.Copy Sheets("mgmnt").Range("A1")
lr = Sheets("mgmnt").Cells(Rows.Count, "A").End(xlUp).Row
    With Sheets("mgmnt").Rows("1:" & lr - 1)
        .Value = .Value
    End With
End Sub
 
Upvote 0
@Vcoppens
Make a sheet called "mgmnt" then use this code


Code:
Sub MM1()
Dim lr As Long
ActiveSheet.UsedRange.Copy Sheets("mgmnt").Range("A1")
lr = Sheets("mgmnt").Cells(Rows.Count, "A").End(xlUp).Row
    With Sheets("mgmnt").Rows("1:" & lr - 1)
        .Value = .Value
    End With
End Sub

Thank you very much MichaelM, Sam-D-Ben and TheRogue: I will be working on your suggestions and see which one does the best job.

Have a nice weekend!
 
Upvote 0

Forum statistics

Threads
1,213,583
Messages
6,114,500
Members
448,575
Latest member
hycrow

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top