Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I paste number formats only?
I'mcopying & pasting data from a database into a monthly report template. The
number formats differ depending on whether I'm copying whole numbers, currency, decimals, etc. There are formulas on the blank template, below the monthly data, to calculate the year-on-year trend. I want to copy JUST the number formats from the 12 monthly cells in one row, down to the 12 formula cells in the next row. Here's my failed attempt at blind-coding this: sub paste_number_formats() ActiveCell.Range("A1:L1").Copy ActiveCell.Offset(1, 0).Range("A1:L1").PasteSpecial _ Paste:=xlPasteNumberFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False end sub Of course, "=xlPasteNumberFormats" was invented by me, so this macro doesn't work, but that's what I'm trying to do anyway. Is this possible? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I paste number formats only?
hi
try this.. Range("A2").Select Range("A2:L2").Copy ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteFormats, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False regards FSt1 "baldmosher" wrote: I'mcopying & pasting data from a database into a monthly report template. The number formats differ depending on whether I'm copying whole numbers, currency, decimals, etc. There are formulas on the blank template, below the monthly data, to calculate the year-on-year trend. I want to copy JUST the number formats from the 12 monthly cells in one row, down to the 12 formula cells in the next row. Here's my failed attempt at blind-coding this: sub paste_number_formats() ActiveCell.Range("A1:L1").Copy ActiveCell.Offset(1, 0).Range("A1:L1").PasteSpecial _ Paste:=xlPasteNumberFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False end sub Of course, "=xlPasteNumberFormats" was invented by me, so this macro doesn't work, but that's what I'm trying to do anyway. Is this possible? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I paste number formats only?
Try:
Sub numFormatter() For Each r In Range("A1:L1") r.Offset(1, 0).NumberFormat = r.NumberFormat Next End Sub -- Gary''s Student - gsnu200851 "baldmosher" wrote: I'mcopying & pasting data from a database into a monthly report template. The number formats differ depending on whether I'm copying whole numbers, currency, decimals, etc. There are formulas on the blank template, below the monthly data, to calculate the year-on-year trend. I want to copy JUST the number formats from the 12 monthly cells in one row, down to the 12 formula cells in the next row. Here's my failed attempt at blind-coding this: sub paste_number_formats() ActiveCell.Range("A1:L1").Copy ActiveCell.Offset(1, 0).Range("A1:L1").PasteSpecial _ Paste:=xlPasteNumberFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False end sub Of course, "=xlPasteNumberFormats" was invented by me, so this macro doesn't work, but that's what I'm trying to do anyway. Is this possible? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I paste number formats only?
Here is one more way...
Dim V As Variant With Range("A1:L6") V = .Offset(1).Value .Copy .Cells(1).Offset(1) .Offset(1) = V End With -- Rick (MVP - Excel) "baldmosher" wrote in message ... I'mcopying & pasting data from a database into a monthly report template. The number formats differ depending on whether I'm copying whole numbers, currency, decimals, etc. There are formulas on the blank template, below the monthly data, to calculate the year-on-year trend. I want to copy JUST the number formats from the 12 monthly cells in one row, down to the 12 formula cells in the next row. Here's my failed attempt at blind-coding this: sub paste_number_formats() ActiveCell.Range("A1:L1").Copy ActiveCell.Offset(1, 0).Range("A1:L1").PasteSpecial _ Paste:=xlPasteNumberFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False end sub Of course, "=xlPasteNumberFormats" was invented by me, so this macro doesn't work, but that's what I'm trying to do anyway. Is this possible? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I paste number formats only?
Hi FSt1,
Unfortunately that also pastes cell borders, which is one of the things I'm trying to avoid. Tom "FSt1" wrote: hi try this.. Range("A2").Select Range("A2:L2").Copy ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteFormats, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False regards FSt1 "baldmosher" wrote: I'mcopying & pasting data from a database into a monthly report template. The number formats differ depending on whether I'm copying whole numbers, currency, decimals, etc. There are formulas on the blank template, below the monthly data, to calculate the year-on-year trend. I want to copy JUST the number formats from the 12 monthly cells in one row, down to the 12 formula cells in the next row. Here's my failed attempt at blind-coding this: sub paste_number_formats() ActiveCell.Range("A1:L1").Copy ActiveCell.Offset(1, 0).Range("A1:L1").PasteSpecial _ Paste:=xlPasteNumberFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False end sub Of course, "=xlPasteNumberFormats" was invented by me, so this macro doesn't work, but that's what I'm trying to do anyway. Is this possible? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I paste number formats only?
Thanks GS, that works a treat. I knew there would be a simple solution! (I'm going to have a go at understanding Rick's solution, but later on when I have more time...!) Thanks all, baldmosher "Gary''s Student" wrote: Try: Sub numFormatter() For Each r In Range("A1:L1") r.Offset(1, 0).NumberFormat = r.NumberFormat Next End Sub -- Gary''s Student - gsnu200851 "baldmosher" wrote: I'mcopying & pasting data from a database into a monthly report template. The number formats differ depending on whether I'm copying whole numbers, currency, decimals, etc. There are formulas on the blank template, below the monthly data, to calculate the year-on-year trend. I want to copy JUST the number formats from the 12 monthly cells in one row, down to the 12 formula cells in the next row. Here's my failed attempt at blind-coding this: sub paste_number_formats() ActiveCell.Range("A1:L1").Copy ActiveCell.Offset(1, 0).Range("A1:L1").PasteSpecial _ Paste:=xlPasteNumberFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False end sub Of course, "=xlPasteNumberFormats" was invented by me, so this macro doesn't work, but that's what I'm trying to do anyway. Is this possible? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy column, paste special formulas & number formats doesn't work | Excel Discussion (Misc queries) | |||
Conditional formats- paste special formats? | Excel Discussion (Misc queries) | |||
paste conditional formats as formats | Excel Discussion (Misc queries) | |||
Paste Number Formats | Excel Discussion (Misc queries) | |||
How to copy and paste number formats only? | Excel Programming |