How to copy and paste number formats only?

[Maestro_J]

Hi,

I want to copy number formats from one range of cells to another. I
don't want other formattings like boarders or colours to be copied and
I also don't want to copy values or formulas.

Until now I found no possibility for that. Is there a similar function
like

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats?

Thanks for your help

 

[davesexcel]

Select the cell that you want to copy, (just the format) and click on
format painter button, (looks like a paintbrush)
then highlite the range you want formatted, there you go, it's done

 

[Maestro_J]

Thanks, I know that function, but it doesn't help. Using the format
painter boarders and colours are also copied

 

[Ardus Petus]

Copy/paste following code into a Module;

'--------------------------------------------------
Dim format As String

Sub CopyNumberFormat()
format = ActiveCell.NumberFormat
End Sub

Sub PasteNumberFormat()
ActiveCell.NumberFormat = format
End Sub

 

[MDubbelboer]

is there anyway to do this on a larger scale?

for example using:

copyarea = SourceSheet.Range(SourceSheet.Cells(firstrow(i), 1)
SourceSheet.Cells(lastrow(i), Last_Column))

DestSheet.Range(DestSheet.Cells(2, 1), DestSheet.Cells(lastrow(i)
firstrow(i) + 2, Last_Column)) = copyarea

Just seems to copy the values, if my source range has differing numbe
formats (i.e. some are text some are numbers) and I use the command
listed everything becomes general.

Any advice

 

[Dave Peterson]

How about using copy|paste special|values, followed by paste special|formats

 

[MDubbelboer]

How about using copy|paste special|values, followed by past
special|formats

Alright. I'm making this harder than it should be:
what is wrong with the following


SourceSheet.Range(SourceSheet.Cells(firstrow(i), 1)
SourceSheet.Cells(lastrow(i), Last_Column)).select
selection.copy

DestSheet.Range(DestSheet.Cells(2, 1), DestSheet.Cells(lastrow(i)
firstrow(i) + 2, Last_Column)).select
selection.pastespecial paste:=xlPasteFormats, Operation:=xlNone
skipblanks:=False, Transpose:=False
selection.pastespecial paste:=xlPasteValues, Operation:=xlNone
skipblanks:=False, Transpose:=False

it was able to copy and paste when I had it as .value but when I tr
this method it's not doing anything..

 

[Dave Peterson]

Maybe...

Dim SourceSheet as Worksheet
dim SourceRng as range
dim LastRow() as long
dim i as long

dim DestSheet as worksheet
dim destcell as range

'something that sets those variables...
'lastrow() and i

set sourcesheet = worksheets("sourceworksheetname")
set destsheet = worksheets("destworksheetnamename")

with sourcesheet
set sourcerng = .range(.cells(firstrow(i),1),.cells(lastrow(i),last_column))
end with

with destsheet
'just use the top left cell of the range
'excel will adjust the range -- just like it does when
'you do it manually
set destcell = .cells(2,1)
end with

sourcerng.copy
destcell.pastespecial paste:=xlpastevalues
destcell.pastespecial paste:=xlpasteformats

===========
All untested and not compiled. I didn't take the time to set up those other
variables. So watch out for typos!