Removing formulas from a sheet but keeping the results

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Removing formulas from a sheet but keeping the results

  1. #1
    Board Regular
    Join Date
    Mar 2003
    Location
    Glasgow
    Posts
    114
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Removing formulas from a sheet but keeping the results

     
    Hello all,

    I have been creating a very large spreadsheet (heavily formated, heavily formulated) over the past financial year.

    I now want to send the results out to people. The ideal thing would be if I could just remove all the formulas from the sheet and keep the results.

    I tried copy and paste, etc but the formating wasn't really working. It would be so easy for me if I could remove the formulas....(many thanks!!!!)

    PS Is formulated a real word or did I just invent a new one?

    Cheers from fugeg70

  2. #2
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,693
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    have you tried copy/ paste special (values)?
    Regards,
    Zack Barresse
    My Book on Excel Tables
    My Blog @ ExcelTables.com
    (If you would like comments in any code, please say so.)

  3. #3
    Board Regular
    Join Date
    Mar 2003
    Location
    Glasgow
    Posts
    114
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Removing formulas from a sheet but keeping the results

    Hi firefytr,

    Many thanks.....

    Yeah I tried first to copy the format then paste special and then to copy the values and paste special them on top. However, it mucked up all my printing and borders, etc. It is an option and may be what I need to do but there is a massive amount of work required to go though and get it all looking exactly the way it does with the formulas in it.....nightmare!

    G.

  4. #4
    Board Regular
    Join Date
    Mar 2003
    Location
    Glasgow
    Posts
    114
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Removing formulas from a sheet but keeping the results

    Hi there,

    I am so stupid.....thanks your answer is perfect!

  5. #5
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Redmond, WA
    Posts
    29,498
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Removing formulas from a sheet but keeping the results

      
    Following on Zacks suggestion, this may speed things up for you a bit. It will Copy-->Paste Special-->Values in each sheet in your workbook.

    Sub CopyValues()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    With ActiveSheet.Cells
    .Copy
    .PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    End With
    With Application
    .CutCopyMode = False
    .Goto Reference:=['Sheet1'!A1]
    End With
    Next ws
    End Sub


    Hope that helps,

    Smitty

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com