Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy previous row down 5 rows
Dim Cnt As Integer
I'm try to insert 5 new rows, then copy the previous row down thru the next 5 rows. I'm have some problem in getting it to work. Need help. Thanks Range("A" & SheetEnd).Select Set CpyCel = EndCel.Offset(-1, 0) Cnt = 5 Do Until Cnt = 0 Selection.EntireRow.Insert Cnt = Cnt - 1 Loop RowStr = "" & CpyCel.Row & ":" & CDec(CpyCel.Row) Selection.EntireRow.Copy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy previous row down 5 rows
Rick,
If I understand what you are trying to do correctly, try this: Cells(SheetEnd - 1, 1).EntireRow.Copy Range(Cells(SheetEnd, 1), Cells(SheetEnd + 4, 1)).Insert Shift:=xlDown Application.CutCopyMode = False If the macro copies the wrong row or inserts in the wrong place, play with the row part of the Cells(.... object. HTH, Matthew Pfluger "Rick" wrote: Dim Cnt As Integer I'm try to insert 5 new rows, then copy the previous row down thru the next 5 rows. I'm have some problem in getting it to work. Need help. Thanks Range("A" & SheetEnd).Select Set CpyCel = EndCel.Offset(-1, 0) Cnt = 5 Do Until Cnt = 0 Selection.EntireRow.Insert Cnt = Cnt - 1 Loop RowStr = "" & CpyCel.Row & ":" & CDec(CpyCel.Row) Selection.EntireRow.Copy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy previous row down 5 rows
Thanks it works ok, except for I want to copy only formula's, how do I alter
your suggestion. "Rick" wrote: Dim Cnt As Integer I'm try to insert 5 new rows, then copy the previous row down thru the next 5 rows. I'm have some problem in getting it to work. Need help. Thanks Range("A" & SheetEnd).Select Set CpyCel = EndCel.Offset(-1, 0) Cnt = 5 Do Until Cnt = 0 Selection.EntireRow.Insert Cnt = Cnt - 1 Loop RowStr = "" & CpyCel.Row & ":" & CDec(CpyCel.Row) Selection.EntireRow.Copy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy previous row down 5 rows
Mathew:
Also the statement Range(Cells(SheetEnd, 1), Cells(SheetEnd + 4, 1)).Insert Shift:=xlDown causes the code to jump out of the current module to the sheet number vba code "Rick" wrote: Dim Cnt As Integer I'm try to insert 5 new rows, then copy the previous row down thru the next 5 rows. I'm have some problem in getting it to work. Need help. Thanks Range("A" & SheetEnd).Select Set CpyCel = EndCel.Offset(-1, 0) Cnt = 5 Do Until Cnt = 0 Selection.EntireRow.Insert Cnt = Cnt - 1 Loop RowStr = "" & CpyCel.Row & ":" & CDec(CpyCel.Row) Selection.EntireRow.Copy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy previous row down 5 rows
Rick, see if you can work with this:
Sub rsz() Dim lstRw As Long lstRw = Cells(Rows.Count, 1).End(xlUp).Row For i = lstRw To 1 Step -1 Cells(i, 1).EntireRow.Copy Range(Cells(i + 1, 1), Cells(i + 5, 1)).EntireRow.PasteSpecial Paste:=xlPasteFormulas If Cells(i, 1).Row < 1 Then Cells(i, 1).Resize(5, 4).EntireRow.Insert End If Next Application.CutCopyMode = False End Sub "Rick" wrote: Dim Cnt As Integer I'm try to insert 5 new rows, then copy the previous row down thru the next 5 rows. I'm have some problem in getting it to work. Need help. Thanks Range("A" & SheetEnd).Select Set CpyCel = EndCel.Offset(-1, 0) Cnt = 5 Do Until Cnt = 0 Selection.EntireRow.Insert Cnt = Cnt - 1 Loop RowStr = "" & CpyCel.Row & ":" & CDec(CpyCel.Row) Selection.EntireRow.Copy |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy previous row down 5 rows
Be careful of the word wrap on one of those lines.
"Rick" wrote: Dim Cnt As Integer I'm try to insert 5 new rows, then copy the previous row down thru the next 5 rows. I'm have some problem in getting it to work. Need help. Thanks Range("A" & SheetEnd).Select Set CpyCel = EndCel.Offset(-1, 0) Cnt = 5 Do Until Cnt = 0 Selection.EntireRow.Insert Cnt = Cnt - 1 Loop RowStr = "" & CpyCel.Row & ":" & CDec(CpyCel.Row) Selection.EntireRow.Copy |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy previous row down 5 rows
Rather than warning OP about word wrap why don't you add a continuation mark in
your original code? Gord Dibben MS Excel MVP On Sat, 10 Nov 2007 18:39:00 -0800, JLGWhiz wrote: Be careful of the word wrap on one of those lines. "Rick" wrote: Dim Cnt As Integer I'm try to insert 5 new rows, then copy the previous row down thru the next 5 rows. I'm have some problem in getting it to work. Need help. Thanks Range("A" & SheetEnd).Select Set CpyCel = EndCel.Offset(-1, 0) Cnt = 5 Do Until Cnt = 0 Selection.EntireRow.Insert Cnt = Cnt - 1 Loop RowStr = "" & CpyCel.Row & ":" & CDec(CpyCel.Row) Selection.EntireRow.Copy |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy previous row down 5 rows
Cause I forgot to do it. :(
"Gord Dibben" wrote: Rather than warning OP about word wrap why don't you add a continuation mark in your original code? Gord Dibben MS Excel MVP On Sat, 10 Nov 2007 18:39:00 -0800, JLGWhiz wrote: Be careful of the word wrap on one of those lines. "Rick" wrote: Dim Cnt As Integer I'm try to insert 5 new rows, then copy the previous row down thru the next 5 rows. I'm have some problem in getting it to work. Need help. Thanks Range("A" & SheetEnd).Select Set CpyCel = EndCel.Offset(-1, 0) Cnt = 5 Do Until Cnt = 0 Selection.EntireRow.Insert Cnt = Cnt - 1 Loop RowStr = "" & CpyCel.Row & ":" & CDec(CpyCel.Row) Selection.EntireRow.Copy |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy previous row down 5 rows
Sorry Gentlemen:
The Code caused the vba sub routine to jump to the vba code of the sheet I'm trying to do the insert & copy too. There is got to be a simple way of cleanning up my original code. It was done by recording a macro by doing the steps manually. "Rick" wrote: Thanks it works ok, except for I want to copy only formula's, how do I alter your suggestion. "Rick" wrote: Dim Cnt As Integer I'm try to insert 5 new rows, then copy the previous row down thru the next 5 rows. I'm have some problem in getting it to work. Need help. Thanks Range("A" & SheetEnd).Select Set CpyCel = EndCel.Offset(-1, 0) Cnt = 5 Do Until Cnt = 0 Selection.EntireRow.Insert Cnt = Cnt - 1 Loop RowStr = "" & CpyCel.Row & ":" & CDec(CpyCel.Row) Selection.EntireRow.Copy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need A Macro To Copy Previous Tab to New Tab | Excel Discussion (Misc queries) | |||
Macro to copy previous row and insert two blank rows | Excel Programming | |||
Macro to copy previous row and insert two blank rows | Excel Discussion (Misc queries) | |||
copy from previous page | Excel Programming | |||
add rows with copy format of previous row | Excel Programming |