How to copy and paste number formats only?

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

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

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

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
-------------------------------------------------

Then associate a keybard shortcut to each of thes macros

HTH
--
AP

"Maestro_J" <m.j.schumacher@web.de> a écrit dans le message de
news:1141730003.768885.135300@p10g2000cwp.googlegroups.com...
> 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
>

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 number formats (i.e. some are text some are numbers) and I use the commands listed everything becomes general.

Any advice?

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



MDubbelboer wrote:
>
> 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 number
> formats (i.e. some are text some are numbers) and I use the commands
> listed everything becomes general.
>
> Any advice?
>
> --
> MDubbelboer
> ------------------------------------------------------------------------
> MDubbelboer's Profile: http://www.excelforum.com/member.php...o&userid=36330
> View this thread: http://www.excelforum.com/showthread...hreadid=519666

--

Dave Peterson

Originally Posted by Dave Peterson

How about using copy|paste special|values, followed by paste 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 try this method it's not doing anything...

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!



MDubbelboer wrote:
>
> Dave Peterson Wrote:
> > How about using copy|paste special|values, followed by paste
> > 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 try
> this method it's not doing anything...
>
> --
> MDubbelboer
> ------------------------------------------------------------------------
> MDubbelboer's Profile: http://www.excelforum.com/member.php...o&userid=36330
> View this thread: http://www.excelforum.com/showthread...hreadid=519666

--

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!



MDubbelboer wrote:
>
> Dave Peterson Wrote:
> > How about using copy|paste special|values, followed by paste
> > 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 try
> this method it's not doing anything...
>
> --
> MDubbelboer
> ------------------------------------------------------------------------
> MDubbelboer's Profile: http://www.excelforum.com/member.php...o&userid=36330
> View this thread: http://www.excelforum.com/showthread...hreadid=519666

--

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!



MDubbelboer wrote:
>
> Dave Peterson Wrote:
> > How about using copy|paste special|values, followed by paste
> > 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 try
> this method it's not doing anything...
>
> --
> MDubbelboer
> ------------------------------------------------------------------------
> MDubbelboer's Profile: http://www.excelforum.com/member.php...o&userid=36330
> View this thread: http://www.excelforum.com/showthread...hreadid=519666

--

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!



MDubbelboer wrote:
>
> Dave Peterson Wrote:
> > How about using copy|paste special|values, followed by paste
> > 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 try
> this method it's not doing anything...
>
> --
> MDubbelboer
> ------------------------------------------------------------------------
> MDubbelboer's Profile: http://www.excelforum.com/member.php...o&userid=36330
> View this thread: http://www.excelforum.com/showthread...hreadid=519666

--

Dave Peterson

Originally Posted by 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
-------------------------------------------------

Then associate a keybard shortcut to each of thes macros

HTH
--
AP

"Maestro_J" <m.j.schumacher@web.de> a écrit dans le message de
news:1141730003.768885.135300@p10g2000cwp.googlegroups.com...
> 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
>

Just found this post, the above solution is elegantly simple and works for me! Thanks very much Ardus Petus, spot on!!