XP Pivot Table

  • Thread starter Thread starter Phil Boffin
  • Start date Start date
P

Phil Boffin

I am having a problem with summing values in a pivot
table. The data is copied from an Access table and XP
seems to decide to format everything as text including
dates and numbers. Can this be switched off so it copies
the same format as it started as? Formatting the cells
doesn't appear to work.

regards

Phil
 
How are you getting the data there. If you are in Tables view in Access XP, you can click on the
table you want, and then do Tools / Office Links / Analyse with Microsoft Excel. This should
create an Excel table of the data, and all data formatting should be preserved as well.
 
Instead of pasting, you can choose Edit>Paste Special, and choose csv or
Text. Or, do a normal paste, and then change the data back to numbers:

1. Select an empty cell on the worksheet
2. Choose Edit>Copy
3. Select the cells that you pasted from Access
4. Choose Edit>Paste Special
5. Select Add, click OK

If you do this frequently, you can use a macro paste as csv:
'=======================
Sub PasteCSV()
ActiveSheet.PasteSpecial Format:="Csv", _
Link:=False, DisplayAsIcon:=False
End Sub
'========================

Or to convert the numbers:
'=============================
Sub ConvertToNumbers()
'by Jon Peltier
Cells(65535, 255).Copy
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlPasteSpecialOperationAdd
End Sub
'=============================
 
Back
Top