PDA

View Full Version : Solved: Paste Special - Custom cell formatting



Chipley
02-25-2008, 02:39 AM
Hi,

The script below is returning unwanted values.

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

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
02-25-2008, 03:43 AM
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

Chipley
02-25-2008, 05:15 AM
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
02-25-2008, 07:33 AM
It worked fine for me in my test.

Chipley
02-25-2008, 11:20 AM
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.

:beerchug:
~Chipley