Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy to next row down....revised.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy to next row down....revised.
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy to next row down....revised.
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy to next row down....revised.
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
3rd and last Revised WorkBook | Excel Discussion (Misc queries) | |||
more than 7 nested if (revised) | Excel Programming | |||
Format excel to revised date automatically when revised | Excel Programming | |||
n or U Revised ? | Excel Worksheet Functions | |||
Revised btnprint1_click() | Excel Programming |