| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert new row above and copy Formatting
Hello from Steved
In My workbook I've inserted 250 rows Question Using below macro I put the cursor A6 to Q6 then use insert to insert a row above, I have now gone to Row A7 to Q7 using the paint brush put the cursor in A6 which using the formatting of row 7 has noe formated the new row A6. Is it possible in VBA to do the above. The reason is instead of inserting 250 rows i only need to insert 1 row. Ok if i type in D6 "90201" and then push the enter key it also will activate VBA to insert the new row. Sub insertrow() Range("A6:Q6").Select Selection.EntireRow.Insert Range("A7:Q7").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Range("A6:Q6").Select End Sub Thankyou. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Insert new row above and copy Formatting
Range("A6:Q6").Select
Selection.EntireRow.Insert Range("A7:Q7").Select Selection.Copy Range("A6:Q6").Select Selection.PasteSpecial "Steved" wrote: Hello from Steved In My workbook I've inserted 250 rows Question Using below macro I put the cursor A6 to Q6 then use insert to insert a row above, I have now gone to Row A7 to Q7 using the paint brush put the cursor in A6 which using the formatting of row 7 has noe formated the new row A6. Is it possible in VBA to do the above. The reason is instead of inserting 250 rows i only need to insert 1 row. Ok if i type in D6 "90201" and then push the enter key it also will activate VBA to insert the new row. Sub insertrow() Range("A6:Q6").Select Selection.EntireRow.Insert Range("A7:Q7").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Range("A6:Q6").Select End Sub Thankyou. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Insert new row above and copy Formatting
Helo from Steved
Thanyou for pointing out Selection.PasteSpecial So I am now partly their. Thankyou "PY & Associates" wrote: Range("A6:Q6").Select Selection.EntireRow.Insert Range("A7:Q7").Select Selection.Copy Range("A6:Q6").Select Selection.PasteSpecial "Steved" wrote: Hello from Steved In My workbook I've inserted 250 rows Question Using below macro I put the cursor A6 to Q6 then use insert to insert a row above, I have now gone to Row A7 to Q7 using the paint brush put the cursor in A6 which using the formatting of row 7 has noe formated the new row A6. Is it possible in VBA to do the above. The reason is instead of inserting 250 rows i only need to insert 1 row. Ok if i type in D6 "90201" and then push the enter key it also will activate VBA to insert the new row. Sub insertrow() Range("A6:Q6").Select Selection.EntireRow.Insert Range("A7:Q7").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Range("A6:Q6").Select End Sub Thankyou. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Insert new row above and copy Formatting
You did not miss pastespecial.
You missed to select where to paste please "Steved" wrote: Helo from Steved Thanyou for pointing out Selection.PasteSpecial So I am now partly their. Thankyou "PY & Associates" wrote: Range("A6:Q6").Select Selection.EntireRow.Insert Range("A7:Q7").Select Selection.Copy Range("A6:Q6").Select Selection.PasteSpecial "Steved" wrote: Hello from Steved In My workbook I've inserted 250 rows Question Using below macro I put the cursor A6 to Q6 then use insert to insert a row above, I have now gone to Row A7 to Q7 using the paint brush put the cursor in A6 which using the formatting of row 7 has noe formated the new row A6. Is it possible in VBA to do the above. The reason is instead of inserting 250 rows i only need to insert 1 row. Ok if i type in D6 "90201" and then push the enter key it also will activate VBA to insert the new row. Sub insertrow() Range("A6:Q6").Select Selection.EntireRow.Insert Range("A7:Q7").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Range("A6:Q6").Select End Sub Thankyou. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Insert new row above and copy Formatting
Hello from Steved
You missed to select where to paste please Yes I put in paste special and ran your sugguestion, yes it will, but I do not to copy the contents off the row I have copied only the formatting. The below works fine for me. Thankyou. Sub InsertRow() Range("A6:Q6").Select Selection.EntireRow.Insert Range("A7:Q7").Select Selection.Copy Range("A6").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.Goto Reference:="R6C4" End Sub "PY & Associates" wrote: You did not miss pastespecial. You missed to select where to paste please "Steved" wrote: Helo from Steved Thanyou for pointing out Selection.PasteSpecial So I am now partly their. Thankyou "PY & Associates" wrote: Range("A6:Q6").Select Selection.EntireRow.Insert Range("A7:Q7").Select Selection.Copy Range("A6:Q6").Select Selection.PasteSpecial "Steved" wrote: Hello from Steved In My workbook I've inserted 250 rows Question Using below macro I put the cursor A6 to Q6 then use insert to insert a row above, I have now gone to Row A7 to Q7 using the paint brush put the cursor in A6 which using the formatting of row 7 has noe formated the new row A6. Is it possible in VBA to do the above. The reason is instead of inserting 250 rows i only need to insert 1 row. Ok if i type in D6 "90201" and then push the enter key it also will activate VBA to insert the new row. Sub insertrow() Range("A6:Q6").Select Selection.EntireRow.Insert Range("A7:Q7").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Range("A6:Q6").Select End Sub Thankyou. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Insert new row above and copy Formatting
see
http://www.mvps.org/dmcritchie/excel/insrtrow.htm If you really want to insert above (a selection) rather than below you can change the macro accordingly. To me copying below is more natural because those are the formats you want to copy. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htmSearch Page: http://www.mvps.org/dmcritchie/excel/search.htm "Steved" wrote in message ... Hello from Steved You missed to select where to paste please Yes I put in paste special and ran your sugguestion, yes it will, but I do not to copy the contents off the row I have copied only the formatting. The below works fine for me. Thankyou. Sub InsertRow() Range("A6:Q6").Select Selection.EntireRow.Insert Range("A7:Q7").Select Selection.Copy Range("A6").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.Goto Reference:="R6C4" End Sub "PY & Associates" wrote: You did not miss pastespecial. You missed to select where to paste please "Steved" wrote: Helo from Steved Thanyou for pointing out Selection.PasteSpecial So I am now partly their. Thankyou "PY & Associates" wrote: Range("A6:Q6").Select Selection.EntireRow.Insert Range("A7:Q7").Select Selection.Copy Range("A6:Q6").Select Selection.PasteSpecial "Steved" wrote: Hello from Steved In My workbook I've inserted 250 rows Question Using below macro I put the cursor A6 to Q6 then use insert to insert a row above, I have now gone to Row A7 to Q7 using the paint brush put the cursor in A6 which using the formatting of row 7 has noe formated the new row A6. Is it possible in VBA to do the above. The reason is instead of inserting 250 rows i only need to insert 1 row. Ok if i type in D6 "90201" and then push the enter key it also will activate VBA to insert the new row. Sub insertrow() Range("A6:Q6").Select Selection.EntireRow.Insert Range("A7:Q7").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Range("A6:Q6").Select End Sub Thankyou. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Insert new row above and copy Formatting
Hello David from Steved
Yes normally I would inserta row below, but in this case I am using a PC Pocket handheld and if you usinh upto 40 to 50 rows a day it is easier just to stay on the new row above a opposed to normal row below. Cheers. "David McRitchie" wrote: see http://www.mvps.org/dmcritchie/excel/insrtrow.htm If you really want to insert above (a selection) rather than below you can change the macro accordingly. To me copying below is more natural because those are the formats you want to copy. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htmSearch Page: http://www.mvps.org/dmcritchie/excel/search.htm "Steved" wrote in message ... Hello from Steved You missed to select where to paste please Yes I put in paste special and ran your sugguestion, yes it will, but I do not to copy the contents off the row I have copied only the formatting. The below works fine for me. Thankyou. Sub InsertRow() Range("A6:Q6").Select Selection.EntireRow.Insert Range("A7:Q7").Select Selection.Copy Range("A6").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.Goto Reference:="R6C4" End Sub "PY & Associates" wrote: You did not miss pastespecial. You missed to select where to paste please "Steved" wrote: Helo from Steved Thanyou for pointing out Selection.PasteSpecial So I am now partly their. Thankyou "PY & Associates" wrote: Range("A6:Q6").Select Selection.EntireRow.Insert Range("A7:Q7").Select Selection.Copy Range("A6:Q6").Select Selection.PasteSpecial "Steved" wrote: Hello from Steved In My workbook I've inserted 250 rows Question Using below macro I put the cursor A6 to Q6 then use insert to insert a row above, I have now gone to Row A7 to Q7 using the paint brush put the cursor in A6 which using the formatting of row 7 has noe formated the new row A6. Is it possible in VBA to do the above. The reason is instead of inserting 250 rows i only need to insert 1 row. Ok if i type in D6 "90201" and then push the enter key it also will activate VBA to insert the new row. Sub insertrow() Range("A6:Q6").Select Selection.EntireRow.Insert Range("A7:Q7").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Range("A6:Q6").Select End Sub Thankyou. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| insert and copy | Excel Worksheet Functions | |||
| Copy and insert | Excel Discussion (Misc queries) | |||
| Copy and paste versus copy and insert copied cells | New Users to Excel | |||
| Move/Copy or Copy/Insert worksheet? | Excel Discussion (Misc queries) | |||
| Copy & Insert from another xls | Excel Discussion (Misc queries) | |||
Linear Mode
