Solved: Paste Special - Custom cell formatting

[Chipley]

Hi,

The script below is returning unwanted values.

[VBA]Sub ValuesToString()
Dim i As Integer
'This will ensure that excel doesn't display
'what it is doing - a bit of performance increase
'for large Excel files
Application.ScreenUpdating = False
'Loop thru all sheets in a workbook
For i = 1 To ActiveWorkbook.Sheets.Count
'Selects all cells in a worksheet
Sheets(i).Cells.Copy
'Paste the copied cells as values instead of formulas
Sheets(i).Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'TODO: Paste special with custom cell formatting
Sheets(i).Cells.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'==================================================
Next i
Application.ScreenUpdating = True
End Sub[/VBA]

I'm trying to modify this script to do the following:

For instance, a cell has this formatting:

The value of cell A1 = Q0.0, A2 = Q0.1, A3 = Q0.2, and so on. These values are created through the Custom cell formatting.

Right-click a cell>Format Cells>Number tab>Custom

The custom formatting for these cells is "Q"0".0". However, when i run the ValuesToStrings function, the custom formatting is lost and all that is displayed in the formula field for a given cell when selected is 0. A1 = 0, A2 = 0, A3 = 0, and so on. The values in the Excel cells look correct, but the formula field needs to be the same as the cell values.

What I would like to see in the formula field when A1, A2, or A3 is selected is Q0.0, Q0.1, or Q0.2 ... , but instead, the formula field is displaying 0 for all 3 cells if that makes any sense. This plays an important role for the next step of this prototype project, which uses a java converter, but that's another story, and not relevant here.

Hope that makes sense.

[Bob Phillips]

[vba]

Sub ValuesToString()
Dim i As Integer
'This will ensure that excel doesn't display
'what it is doing - a bit of performance increase
'for large Excel files
Application.ScreenUpdating = False
'Loop thru all sheets in a workbook
For i = 1 To ActiveWorkbook.Sheets.Count
'Selects all cells in a worksheet
Sheets(i).UsedRange.Value = Sheets(i).UsedRange.Value
Next i
Application.ScreenUpdating = True
End Sub
[/vba]

[Chipley]

hi xld,

thanks for the reply. Unfortunately, this script is not carrying over the cell formatting of Q0.0, the formula field still only displays 0.

I'm working with some other pals on this at the moment as well, getting closer but still no resolve...

[Bob Phillips]

It worked fine for me in my test.

[Chipley]

Hi,

Thanks for you help, xld. I didn't explain my problem well enough here, sorry for that.

I have found a solution and thought I'd post it for the rest of the community, many thanks to koolsid for the code, who operates on another VB forum:

Sub ValuesToString()
    Application.ScreenUpdating = False
 
    Dim dataSheet As Worksheet
    Dim dataCell As Range
 
    For Each dataSheet In ActiveWorkbook.Sheets
    'Selects all cells in a worksheet
        dataSheet.Cells.Copy
        'Paste the copied cells as values instead of formulas
        dataSheet.Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
        dataSheet.Cells.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Next dataSheet
 
    'Loops through all sheets in a workbork
    For Each dataSheet In ActiveWorkbook.Sheets
        'the variable dataSheet now refers to a particular sheet in ActiveWorkbook
        For Each dataCell In dataSheet.UsedRange.Cells
        'Converts values in cells to text string
            dataCell.Cells(1).Value = dataCell.Cells(1).Text
        Next dataCell
    Next dataSheet
 
    Application.ScreenUpdating = True
 
End Sub

I'm sure I'll be back soon with problems, being a n000b and all.


~Chipley