Excel2000: Insert a copy of row range

[Arvi Laanemets]

Hi

I need in VBA to add a 5-row range into a table (5 rows is a entry in
table).
The operation will be same as manually to copy a row range, and then insert
copied cells to same location. All formats (cell merging included),
formulas, row heights, etc. must be copied too.

The code below copies the same range (initially, it has to be a pre-last
entry in table - using the last one makes a mess from all formulas) over,
instead inserting a new one. How to modify it?

.....
For i = 1 To (varNeededEntries - varCurrentEntries)
Sheets("Sheet1").Range(RowX & ":" & (RowX+4)).Copy
(Sheets("Sheet1").Range(RowX & ":" & (RowX+4)))
Next i
.....


Thanks in advance!

 

[Tom Ogilvy]

Nothing in your loop changes. You perform the same action 1 to N times.
Makes no sense.

You need to increment your RowX within the loop. Also, you copy from one
location to the same location. That makes no sense either. You shouldn't
have the destination range in parentheses.

Other than that, your description offers little insight into what your
problem is.

 

[Arvi Laanemets]

Hi


I almost have it!
......
For i = 1 To (varNeededEntries - varCurrentEntries)
Sheets("Sheet1").Range(RowX & ":" & (RowX+4)).Insert
(Sheets("Leht1").Range(RowX & ":" & (RowX+4)).Copy)
Next i
......

, but this copies all except cell merging. How to make this to copy cell
merging too? Manually it is copied! Below is manul operation saved as a
macro

Sub Macro3()
Rows("111:115").Select
Range("D111").Activate
' cells in ranges A111:A114, B111:B114 and C111:C114 are merged
Selection.Copy
Selection.Insert Shift:=xlDown
End Sub

 

[Arvi Laanemets]

Hi again

I try to explain what I want. I have a table, where rows "1:10" are reserved
for header. Let's assume the table is prepared for 5 entries - 5 rows for
every entry, so I have rows "11:35" prepared for user + there are some
additional rows (summary data, remarks, etc.) at bottom of table. Now I want
the table to be prepared for 10 entries. The procedure copies 5 times the
range "26:30", and inserts copied range at same position - the bottom part
of table is shifted down for 5 rows every time.

(The case, where the number of entries was reduced, was a easy one)

 

[Tom Ogilvy]

If it doesn't copy the merged cells, but it otherwise does what you want,
then just have your code merge the proper cells. This sounds like something
you know and will not change. In the code you show, I see you inserting
rows and copying rows, but not inserting the copied rows.

also, you don't need to loop

Sub abc()
Dim destrow As Long
Dim numtimes As Long
Dim numrows As Long
destrow = 26
numtimes = 5
numrows = 5
Worksheets("Leht1").Rows(11).Resize(numrows).Copy
Worksheets("Sheet1").Rows(destrow) _
.Resize(numtimes * numrows).Insert
' if necessary
With Worksheets("sheet1")
For i = destrow To destrow + (numtimes * numrows) - 1 Step numtimes
.Cells(i, 1).Resize(numrows, 1).Merge
.Cells(i, 2).Resize(numrows, 1).Merge
.Cells(i, 3).Resize(numrows, 1).Merge
Next
End With

End Sub



would be the type of approach I would take.