Copy to next row down....revised.

[pickytweety]

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

 

[Dave Peterson]

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]

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

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

 

[Dave Peterson]

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

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?