Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000: Insert a copy of row range
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! -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000: Insert a copy of row range
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. -- Regards, Tom Ogilvy "Arvi Laanemets" wrote in message ... 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! -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000: Insert a copy of row range
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 ( My real mail address: arvi.laanemets<attarkon.ee ) "Tom Ogilvy" wrote in message ... 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. -- Regards, Tom Ogilvy "Arvi Laanemets" wrote in message ... 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! -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000: Insert a copy of row range
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) -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000: Insert a copy of row range
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. -- Regards, Tom Ogilvy "Arvi Laanemets" wrote in message ... 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 ( My real mail address: arvi.laanemets<attarkon.ee ) "Tom Ogilvy" wrote in message ... 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. -- Regards, Tom Ogilvy "Arvi Laanemets" wrote in message ... 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! -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel2000: Formatting a range as Text in VBA | Excel Programming | |||
Excel2000: How to copy data validation using VBA | Excel Programming | |||
Excel2000: Reading Named Range value from VBA | Excel Programming | |||
Excel2000: Copy all exept data | Excel Programming | |||
Excel2000: finding last row of used range | Excel Programming |