CODEWITHSUNDEEP
vbaexcel
Crna Krv
Crna Krv

Reputation: 147

After data update, preserve cell values instead of formulas and delete data sheet

I have a xlsx spreadsheet with a bunch of sheets that either contain data or formulas. Lets say sheet1 has the data and sheet2 has formulas referring to the data in sheet1. I'm trying to do the following when sheet1 is updated with new data (coming from a SAS program):

  1. convert sheet2 to only values (i.e. remove the formulas behind)
  2. delete sheet1
  3. save file

I would need to automate this througout the spreadsheet and have the macro/program run automaticcaly when there's an update.

So far, here's what I got (pasting values instead of formulas):

Sub Sample()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("tab2")

    ws.UsedRange.Value = ws.UsedRange.Value
End Sub

I am really not familiar with VBA.

Upvotes: 0

Views: 51

Answers (3)

CSSWormy
CSSWormy

Reputation: 65

Why not just scratch the idea of two sheets and extrapolate on the answer I got in my question.

Use formula to update cells, but as values

Try a macro with 

Range("A1:P10").Select
With Selection
    .Value = .Value
End With

This will turn every formula in the range you've selected into it's value. Just don't try using Cells.Select. When I tried it, I got a memory related error. If your spreadsheets are various sizes, then calculate the bottom cell first.

Sub Convert()
    ' Table Bottom
    Dim TB As Long
    ' Set Table Bottom Variable
    TB = Range("A65000").End(xlUp).Row 'finds bottom row
    ' Change Formulas in Range to Values
    Range("A1:P" & TB).Select
    With Selection
        .Value = .Value
    End With
End Sub

Sometimes I have to change the column for finding the bottom row depending on the spreadsheet, but this is the general idea.

I think this will accomplish what you're ultimately trying to do if you put this at the end of the code that is pulling the updates, or if you just run it separately after the updates have been completed. It doesn't seem like your goal is to have two different spreadsheets. Just one spreadsheet that is only values.

Upvotes: 0

Cyril
Cyril

Reputation: 6829

You've got the values only in Sheet2.

You want to delete Sheet1 (there should be a pop-up to check if you really want to delete the sheet... we're going to turn that off before you do, then back on after):

Application.DisplayAlerts = False 
Sheets("Sheet1").Delete
Application.DisplayAlerts = True

Then to .SaveAs (prompts for file name to save as)

ThisWorkbook.SaveAs Filename:=fName

Upvotes: 1

Vinnie
Vinnie

Reputation: 553

Try this: 

Sub Sample()
        Dim ws As Worksheet

        Set ws = ThisWorkbook.Sheets("tab2")

        ws.UsedRange.Value = ws.UsedRange.Value

        Application.DisplayAlerts = False
            Sheets("sheet1").Delete
        Application.DisplayAlerts = True

        ActiveWorkbook.Save

    End Sub

Upvotes: 0

Related Questions