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?

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?

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?

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" <(E-Mail Removed)> wrote in message
news:10D222AB-2531-426A-A488-(E-Mail Removed)...
> 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?

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?

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?