Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A visual basic value copy BUG?? - accounting format has copy problem!! | Excel Programming | |||
Copy/Paste how to avoid the copy of formula cells w/o calc values | Excel Discussion (Misc queries) | |||
copy formulas from a contiguous range to a safe place and copy them back later | Excel Programming | |||
EXCEL FILE a copy/a copy/a copy ....filename | New Users to Excel | |||
Code to copy range vs Copy Entire Worksheet - can't figure it out | Excel Programming |