Avatar of k1ng87
k1ng87
 asked on

how save cell values only not using copy/past values method

is there a way to save the values only in an excel file without using the copy/paste values method? I have a huge excel file (>300mb) that has a pivot table which feeds off of a sql server. the pivot table then feeds the other worksheets in the work book. currently, after I refresh my pivot table with certain criteria, I run a macro that goes through all the worksheets and does a "copy/paste values" so that I loose the formulas and makes the workbook apprx <3mb.
Microsoft Server OSMicrosoft Excel

Avatar of undefined
Last Comment
Ardhendu Sarangi

8/22/2022 - Mon
Ardhendu Sarangi

Hi,

There is another option to get around Copy/Paste Values.

Assuming you want to remove the formulas and just save the values in cell A1, then you can use something like this -

Range("A1") = Range("A1")

This will replace the formula in A1 with the value.

If you want to do this for the whole sheet, try the following code.

- Ardhendu
Sub code()

For i = 1 To Cells(65536, "A").End(xlUp).Row
    For j = 1 To Cells(i, 256).End(xlToLeft).Column
        Cells(i, j) = Cells(i, j)
     Next
Next

End Sub

Open in new window

k1ng87

ASKER
Could you explain this code please? And how would I do this for multiple sheets. For instance, if I had sheetA, sheetB, and sheetC.
Ardhendu Sarangi

There are two loops in this code...

1. the first loop starts from the first row and moves down till the last row of data
2. the 2nd loop starts from the first column (Column A) and then moves right till the last column of data
3. within these loops, i am replacing each cell with the value and cleaning out the formulas.

Does that make sense?

to make this work for all the sheets, you would need to add something like this..

Thanks,
Ardhendu
Sub code()
    Dim i As Integer, n As Integer
    For n = 1 To Worksheets.Count
        Worksheets(n).Select
        For i = 1 To Cells(65536, "A").End(xlUp).Row
            For j = 1 To Cells(i, 256).End(xlToLeft).Column
                Cells(i, j) = Cells(i, j)
            Next
        Next
    Next
End Sub

Open in new window

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Ardhendu Sarangi

To install the code, use ALT + F11 to open the VBA Editor. Click on Insert...Module to create a blank module sheet. Paste the code there, then ALT + F11 to return to the spreadsheet.

To run the code, hit ALT + F8 to open the macro window. Select the macro, then click the Run button.

If the above procedure doesn't work, then you need to change your macro security setting. To do so, open the Tools...Macro...Security menu item. Choose Medium, then click OK."    

NOTE
1. This works on the assumption that you have data starting from Column A in all the worksheets.
2. If you have a lot of sheets and a lot of cells, this will take a while to run.



Thanks,
Ardhendu
k1ng87

ASKER
couple questions...

First, do I need to set "j" as an integer?

Second I'm assuming that "Cells(i, j) = Cells(i, j)"  is whats converting the formula to a value only but is this the same as copy/paste values?

Third, if i have 5 worksheets and i just want to run this for the first 3, do I just set this for each worksheet and not a loop?
k1ng87

ASKER
is this correct for my situation?
Sub code() 
    Dim i As Integer
      Worksheets(Array("sheet1", "sheet2", "sheet3").Select 
        For i = 1 To Cells(65536, "A").End(xlUp).Row 
            For j = 1 To Cells(i, 256).End(xlToLeft).Column 
                Cells(i, j) = Cells(i, j) 
            Next 
        Next 
End Sub

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Ardhendu Sarangi

Hi,
1. you need to set j as integer.

2. > Second I'm assuming that "Cells(i, j) = Cells(i, j)"  is whats converting the formula to a value only but is this the same as copy/paste values?

That is correct.

3. If you want to do this for only three worksheets then you would have to call the worksheets by name for this. Your code will not work and will give you an application error.

You can use something like this instead.

- Ardhendu





Sub code()
    Dim i As Integer, n As Integer, j As Integer
    For n = 1 To Worksheets.Count
        If Worksheets(n).Name <> "YOUR NAME HERE" And Worksheets(n).Name <> "2nd Name" Then
            Worksheets(n).Select
            For i = 1 To Cells(65536, "A").End(xlUp).Row
                For j = 1 To Cells(i, 256).End(xlToLeft).Column
                    Cells(i, j) = Cells(i, j)
                Next
            Next
        End If
    Next
End Sub

Open in new window

k1ng87

ASKER
I tried that code but I get an error at line 8 saying "Cannot enter a formula for an item or field name in a pivottable report." I dont understand how its going to the pivot table as its not referenced in teh code.
Ardhendu Sarangi

can you show me the code u have for your copy/paste logic?

Also, I really don't see the point as to why are you looking for an alternative, because this code will still be doing the same stuff as your existing code.  can you elaborate?

Thanks,
Ardhendu
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
k1ng87

ASKER
I was jsut trying to see if there was a "shorter" code for this...but here is my code which does work.
Sheets("Summary").Select
        Cells.Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Dom_Shipment_Char").Select
        Cells.Select
        Range("C22").Activate
        Application.CutCopyMode = False
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Intl_Shipment_Char").Select
        Cells.Select
        Range("B7").Activate
        Application.CutCopyMode = False
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Accessorials").Select
        Cells.Select
        Range("C107").Activate
        Application.CutCopyMode = False
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Customs, Duty and Tax").Select
        Cells.Select
        Range("I4").Activate
        Application.CutCopyMode = False
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

Open in new window

ASKER CERTIFIED SOLUTION
Ardhendu Sarangi

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.