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

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

Select allOpen in new window

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

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

Select allOpen in new window

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

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?

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

Select allOpen in new window

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

Select allOpen in new window

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.

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

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

Select allOpen in new window