Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying a row and pasting the same below it with just formulas
Hi all,
I have 3 buttons in the same worksheet to copy a row and insert the same below it. I have this macro running for 3 different places. See my code below. The problem is: 1) I want just the formulas/formatting to be pasted, and not the constants. 2) And more importantly, if I use Button 1, then a row is added below row 15 and the rows move down and so for Button 2, row 36 is different now as it has moved down, if you understand? How do I keep it fixed that it always copies row 36 even if a row is added above and the rows move down. Same for Button 3. Private Sub CommandButton1_Click() Range("15:15").Select Selection.Copy Selection.Insert Shift:=xlDown End Sub Private Sub CommandButton2_Click() Range("36:36").Select Selection.Copy Selection.Insert Shift:=xlDown End Sub Private Sub CommandButton3_Click() Range("41:41").Select Selection.Copy Selection.Insert Shift:=xlDown End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying a row and pasting the same below it with just formulas
Thanks Don, but my problem is that I want to copy and paste the row
with formulas. So, rows 15, 36 and 41 have formulas.....but when I run the button for row 15, a new row (which is a copy of row 15) is added and row 36 becomes 37, but the macro is still using the row 36 and not changing itself to copy row 37 instead. I tried naming the cell, but still the same thing happens. I think because I am copying the row, your code won`t work as it just inserts a new row. Thanks, Shivam On Feb 12, 5:05*pm, "Don Guillett" wrote: That is what is happening now. You are inserting at row 36. If you mean you want to insert where the original row 36 is NOW then name that cell rtm1 (select cell in name box (left of formula box) type in rtm1. Now use this change and a row will be inserted wherever that cell is Sub insertatfixedrow() 'Range("36:36").Select ' * *Selection.Copy ' * *Selection.Insert Shift:=xlDown 'use this instead Range("rtm1").EntireRow.Insert end sub -- Don Guillett Microsoft MVP Excel SalesAid Software " wrote in message ... Hi all, I have 3 buttons in the same worksheet to copy a row and insert the same below it. I have this macro running for 3 different places. See my code below. The problem is: 1) I want just the formulas/formatting to be pasted, and not the constants. 2) And more importantly, if I use Button 1, then a row is added below row 15 and the rows move down and so for Button 2, row 36 is different now as it has moved down, if you understand? How do I keep it fixed that it always copies row 36 even if a row is added above and the rows move down. Same for Button 3. Private Sub CommandButton1_Click() * *Range("15:15").Select * *Selection.Copy * *Selection.Insert Shift:=xlDown End Sub Private Sub CommandButton2_Click() * *Range("36:36").Select * *Selection.Copy * *Selection.Insert Shift:=xlDown End Sub Private Sub CommandButton3_Click() * *Range("41:41").Select * *Selection.Copy * *Selection.Insert Shift:=xlDown End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying a row and pasting the same below it with just formulas
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Software "Shivam.Shah" wrote in message ... Thanks Don, but my problem is that I want to copy and paste the row with formulas. So, rows 15, 36 and 41 have formulas.....but when I run the button for row 15, a new row (which is a copy of row 15) is added and row 36 becomes 37, but the macro is still using the row 36 and not changing itself to copy row 37 instead. I tried naming the cell, but still the same thing happens. I think because I am copying the row, your code won`t work as it just inserts a new row. Thanks, Shivam On Feb 12, 5:05�pm, "Don Guillett" wrote: That is what is happening now. You are inserting at row 36. If you mean you want to insert where the original row 36 is NOW then name that cell rtm1 (select cell in name box (left of formula box) type in rtm1. Now use this change and a row will be inserted wherever that cell is Sub insertatfixedrow() 'Range("36:36").Select ' � �Selection.Copy ' � �Selection.Insert Shift:=xlDown 'use this instead Range("rtm1").EntireRow.Insert end sub -- Don Guillett Microsoft MVP Excel SalesAid Software " wrote in message ... Hi all, I have 3 buttons in the same worksheet to copy a row and insert the same below it. I have this macro running for 3 different places. See my code below. The problem is: 1) I want just the formulas/formatting to be pasted, and not the constants. 2) And more importantly, if I use Button 1, then a row is added below row 15 and the rows move down and so for Button 2, row 36 is different now as it has moved down, if you understand? How do I keep it fixed that it always copies row 36 even if a row is added above and the rows move down. Same for Button 3. Private Sub CommandButton1_Click() � �Range("15:15").Select � �Selection.Copy � �Selection.Insert Shift:=xlDown End Sub Private Sub CommandButton2_Click() � �Range("36:36").Select � �Selection.Copy � �Selection.Insert Shift:=xlDown End Sub Private Sub CommandButton3_Click() � �Range("41:41").Select � �Selection.Copy � �Selection.Insert Shift:=xlDown End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying and Pasting Different Formulas | Excel Programming | |||
Copying/Pasting Formulas | Excel Discussion (Misc queries) | |||
Copying and pasting multiple formulas, it it possible? | Excel Discussion (Misc queries) | |||
Copying & pasting formulas | Excel Worksheet Functions | |||
Pasting formulas without copying file ref. | Excel Programming |