Copy to next row down
 

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.



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


Sub CopyToNext(wks As Worksheet)

Dim rngfill As Range

With wks
wksSummary.Outline.ShowLevels RowLevels:=2, ColumnLevels:=2
wksSummary.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

Copy to next row down
 

Please see the revised version of this posting.
--
Thanks,
PTweety


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



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


Sub CopyToNext(wks As Worksheet)

Dim rngfill As Range

With wks
wksSummary.Outline.ShowLevels RowLevels:=2, ColumnLevels:=2
wksSummary.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