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

How to copy and paste number formats only?
 

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


--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=519666

How to copy and paste number formats only?
 

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

How to copy and paste number formats only?
 

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" a écrit dans le message de
oups.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

How to copy and paste number formats only?
 

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

--
MDubbelboe
-----------------------------------------------------------------------
MDubbelboer's Profile: http://www.excelforum.com/member.php...fo&userid=3633
View this thread: http://www.excelforum.com/showthread.php?threadid=51966

How to copy and paste number formats only?
 

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

How to copy and paste number formats only?
 

Dave Peterson Wrote:
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..

--
MDubbelboe
-----------------------------------------------------------------------
MDubbelboer's Profile: http://www.excelforum.com/member.php...fo&userid=3633
View this thread: http://www.excelforum.com/showthread.php?threadid=51966

How to copy and paste number formats only?
 

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),las t_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