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


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

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

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

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

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