Hi,
I have a macro that scrolls through a list of stores, pulling up data
regarding each particular store.
There are two different summary sheets, Summary1 and Summary2. In each
summary sheet Row 3 references other areas of the workbook and provides
different statistics for the particular store. I need the macro to copy row
3 in each sheet and paste it as a value to the first blank row after row 6,
then move on to the next store. The store looping is handled in the "other
code" listed below and that seems to be working. The section of code below
isn't copying and pasting as I was hoping. Is there a different way to write
it? Once again, I just want to copy row three (which changes with each
store) and paste it to row 6, 7, 8, 9 ....and so on--keeping in mind that I
need to do it in both summary sheets. Oh...and I'm hoping to get something
that works in both Excel 2003 and Excel 2007.


'other code.....
CopyToNext wksSummary
CopyToNext wksSummary2
'other code


Sub CopyToNext(wks As Worksheet)

Dim rngfill As Range

With wks
.Outline.ShowLevels RowLevels:=2, ColumnLevels:=2
.Calculate
Set rngfill = Nothing
Set rngfill = .Range("A" & .Rows.Count).End(xlUp)
Set rngfill = rngfill.Offset(1, 0)

Rows("3:3").Copy
rngfill.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

rngfill.PasteSpecial Paste:=xlFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False
End With

End Sub


--
Thanks,
PTweety

My first guess is this is the line causing trouble:

Rows("3:3").Copy

I'd use:

.Rows("3:3").Copy

Then it's copying row 3 of wks.

Is that what you wanted?

Actually, I'd use:

.rows(3).copy



pickytweety wrote:
>
> Hi,
> I have a macro that scrolls through a list of stores, pulling up data
> regarding each particular store.
> There are two different summary sheets, Summary1 and Summary2. In each
> summary sheet Row 3 references other areas of the workbook and provides
> different statistics for the particular store. I need the macro to copy row
> 3 in each sheet and paste it as a value to the first blank row after row 6,
> then move on to the next store. The store looping is handled in the "other
> code" listed below and that seems to be working. The section of code below
> isn't copying and pasting as I was hoping. Is there a different way to write
> it? Once again, I just want to copy row three (which changes with each
> store) and paste it to row 6, 7, 8, 9 ....and so on--keeping in mind that I
> need to do it in both summary sheets. Oh...and I'm hoping to get something
> that works in both Excel 2003 and Excel 2007.
>
> 'other code.....
> CopyToNext wksSummary
> CopyToNext wksSummary2
> 'other code
>
> Sub CopyToNext(wks As Worksheet)
>
> Dim rngfill As Range
>
> With wks
> .Outline.ShowLevels RowLevels:=2, ColumnLevels:=2
> .Calculate
> Set rngfill = Nothing
> Set rngfill = .Range("A" & .Rows.Count).End(xlUp)
> Set rngfill = rngfill.Offset(1, 0)
>
> Rows("3:3").Copy
> rngfill.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
> SkipBlanks:=False, Transpose:=False
>
> rngfill.PasteSpecial Paste:=xlFormats, Operation:=xlNone, _
> SkipBlanks:=False, Transpose:=False
>
> Application.CutCopyMode = False
> End With
>
> End Sub
>
> --
> Thanks,
> PTweety

--

Dave Peterson

Exactamundo! Thanks so much--you've saved me from my very bad mood. I had
tried the "period" before "Rows("3:3").Copy" and that didn't work, but the
".rows(3).Copy" worked wonderfully! Why is that? What's the difference?
--
Thanks,
PTweety


"Dave Peterson" wrote:

> My first guess is this is the line causing trouble:
>
> Rows("3:3").Copy
>
> I'd use:
>
> .Rows("3:3").Copy
>
> Then it's copying row 3 of wks.
>
> Is that what you wanted?
>
> Actually, I'd use:
>
> .rows(3).copy
>
>
>
> pickytweety wrote:
> >
> > Hi,
> > I have a macro that scrolls through a list of stores, pulling up data
> > regarding each particular store.
> > There are two different summary sheets, Summary1 and Summary2. In each
> > summary sheet Row 3 references other areas of the workbook and provides
> > different statistics for the particular store. I need the macro to copy row
> > 3 in each sheet and paste it as a value to the first blank row after row 6,
> > then move on to the next store. The store looping is handled in the "other
> > code" listed below and that seems to be working. The section of code below
> > isn't copying and pasting as I was hoping. Is there a different way to write
> > it? Once again, I just want to copy row three (which changes with each
> > store) and paste it to row 6, 7, 8, 9 ....and so on--keeping in mind that I
> > need to do it in both summary sheets. Oh...and I'm hoping to get something
> > that works in both Excel 2003 and Excel 2007.
> >
> > 'other code.....
> > CopyToNext wksSummary
> > CopyToNext wksSummary2
> > 'other code
> >
> > Sub CopyToNext(wks As Worksheet)
> >
> > Dim rngfill As Range
> >
> > With wks
> > .Outline.ShowLevels RowLevels:=2, ColumnLevels:=2
> > .Calculate
> > Set rngfill = Nothing
> > Set rngfill = .Range("A" & .Rows.Count).End(xlUp)
> > Set rngfill = rngfill.Offset(1, 0)
> >
> > Rows("3:3").Copy
> > rngfill.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
> > SkipBlanks:=False, Transpose:=False
> >
> > rngfill.PasteSpecial Paste:=xlFormats, Operation:=xlNone, _
> > SkipBlanks:=False, Transpose:=False
> >
> > Application.CutCopyMode = False
> > End With
> >
> > End Sub
> >
> > --
> > Thanks,
> > PTweety
>
> --
>
> Dave Peterson
>

I think something else broke when you tried ".rows("3:3").copy". That line
should have worked ok.



pickytweety wrote:
>
> Exactamundo! Thanks so much--you've saved me from my very bad mood. I had
> tried the "period" before "Rows("3:3").Copy" and that didn't work, but the
> ".rows(3).Copy" worked wonderfully! Why is that? What's the difference?
> --
> Thanks,
> PTweety
>
> "Dave Peterson" wrote:
>
> > My first guess is this is the line causing trouble:
> >
> > Rows("3:3").Copy
> >
> > I'd use:
> >
> > .Rows("3:3").Copy
> >
> > Then it's copying row 3 of wks.
> >
> > Is that what you wanted?
> >
> > Actually, I'd use:
> >
> > .rows(3).copy
> >
> >
> >
> > pickytweety wrote:
> > >
> > > Hi,
> > > I have a macro that scrolls through a list of stores, pulling up data
> > > regarding each particular store.
> > > There are two different summary sheets, Summary1 and Summary2. In each
> > > summary sheet Row 3 references other areas of the workbook and provides
> > > different statistics for the particular store. I need the macro to copy row
> > > 3 in each sheet and paste it as a value to the first blank row after row 6,
> > > then move on to the next store. The store looping is handled in the "other
> > > code" listed below and that seems to be working. The section of code below
> > > isn't copying and pasting as I was hoping. Is there a different way to write
> > > it? Once again, I just want to copy row three (which changes with each
> > > store) and paste it to row 6, 7, 8, 9 ....and so on--keeping in mind that I
> > > need to do it in both summary sheets. Oh...and I'm hoping to get something
> > > that works in both Excel 2003 and Excel 2007.
> > >
> > > 'other code.....
> > > CopyToNext wksSummary
> > > CopyToNext wksSummary2
> > > 'other code
> > >
> > > Sub CopyToNext(wks As Worksheet)
> > >
> > > Dim rngfill As Range
> > >
> > > With wks
> > > .Outline.ShowLevels RowLevels:=2, ColumnLevels:=2
> > > .Calculate
> > > Set rngfill = Nothing
> > > Set rngfill = .Range("A" & .Rows.Count).End(xlUp)
> > > Set rngfill = rngfill.Offset(1, 0)
> > >
> > > Rows("3:3").Copy
> > > rngfill.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
> > > SkipBlanks:=False, Transpose:=False
> > >
> > > rngfill.PasteSpecial Paste:=xlFormats, Operation:=xlNone, _
> > > SkipBlanks:=False, Transpose:=False
> > >
> > > Application.CutCopyMode = False
> > > End With
> > >
> > > End Sub
> > >
> > > --
> > > Thanks,
> > > PTweety
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson