Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
DREAM FEATU A macro that moves a row but asks me where to move it
I would love to have a macro, that when a button is pushed, will move the selected row (or A:AP) and asked me where to move it. I don't know if a pop-up message box with buttons to push or a combo box with selections to click or another way is best, but based on the selections picked would move the row either to a new place on the current sheet or to another sheet. For example:
"Potential" = sheet2 (inserted above row 3) "Future" = sheet3 (inserted above row 3) "Ageement Sent" = insert above row 3 on current sheet When the macro has completed, I would like the previous row not to be left empty but to be deleted. M I asking too much of Excel? Thanks in advance, magmike |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
DREAM FEATU A macro that moves a row but asks me where to move it
I would love to have a macro, that when a button is pushed, will move
the selected row (or A:AP) and asked me where to move it. I don't know if a pop-up message box with buttons to push or a combo box with selections to click or another way is best, but based on the selections picked would move the row either to a new place on the current sheet or to another sheet. For example: "Potential" = sheet2 (inserted above row 3) "Future" = sheet3 (inserted above row 3) "Ageement Sent" = insert above row 3 on current sheet When the macro has completed, I would like the previous row not to be left empty but to be deleted. M I asking too much of Excel? Thanks in advance, magmike Check out Application.InputBox! This will allow users to select a range in any sheet of any open workbook, simply by setting its 'Type' property. Your code should grab a ref to the currently selected row, use 'Cut' and specify the range returned by Application.InputBox as the destination to 'Insert cut cells'. Presumably, you want the shift 'xlDown'! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
DREAM FEATU A macro that moves a row but asks me where to move it
Can you give me an example of how to use it?
On Wednesday, December 18, 2013 9:53:00 AM UTC-6, magmike wrote: I would love to have a macro, that when a button is pushed, will move the selected row (or A:AP) and asked me where to move it. I don't know if a pop-up message box with buttons to push or a combo box with selections to click or another way is best, but based on the selections picked would move the row either to a new place on the current sheet or to another sheet. For example: "Potential" = sheet2 (inserted above row 3) "Future" = sheet3 (inserted above row 3) "Ageement Sent" = insert above row 3 on current sheet When the macro has completed, I would like the previous row not to be left empty but to be deleted. M I asking too much of Excel? Thanks in advance, magmike |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
DREAM FEATU A macro that moves a row but asks me where to move it
Can you give me an example of how to use it?
Uh! There's examples in the online help. I expect that you don't need spoon feeding given the amount of programming you've done over the past several years. Otherwise, I don't have a generic example made up but can do if need be and time permits<g... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
DREAM FEATU A macro that moves a row but asks me where to move it
I am not a programmer. I am smart enough to modify code for my own uses and even that is limited. Have no training in code or how to build it from scratch, etc. I have looked at the example and haven't the slightest idea how to implement it. I'm guessing, though, that when I call it with the click of my button a box will come up asking me for coordinates and I'll have to type in something like "sheet2!A3:AP3", correct? Will that overwrite what is there or insert it above the current A3:AP3?
How would I predefine 3 or 4 options, and cause the prompt that comes up to give me a choice of those? On Wednesday, December 18, 2013 12:14:27 PM UTC-6, GS wrote: Can you give me an example of how to use it? Uh! There's examples in the online help. I expect that you don't need spoon feeding given the amount of programming you've done over the past several years. Otherwise, I don't have a generic example made up but can do if need be and time permits<g... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
DREAM FEATU A macro that moves a row but asks me where to move it
I am not a programmer. I am smart enough to modify code for my own
uses and even that is limited. Have no training in code or how to build it from scratch, etc. I have looked at the example and haven't the slightest idea how to implement it. I'm guessing, though, that when I call it with the click of my button a box will come up asking me for coordinates and I'll have to type in something like "sheet2!A3:AP3", correct? Will that overwrite what is there or insert it above the current A3:AP3? How would I predefine 3 or 4 options, and cause the prompt that comes up to give me a choice of those? Ah! In this case a turnkey solution is needed, then. If I correctly understand your task then... 1. select the row[s] to move 2. click a menu/button to display a dialog that lets you select the destination row on another sheet in the same workbook OR any open workbook. 3. When you click OK on the dialog the selected row[s] are moved. ...is what you want the macro to do. Sub MoveRows() Dim rngSource As Range, rngTarget As Range Set rngSource = Selection.EntireRow Set rngTarget = Application.InputBox(Prompt:="Select the destination row position", Title:="Move Rows", Type:=8) rngSource.EntireRow.Cut rngTarget End Sub -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
DREAM FEATU A macro that moves a row but asks me where to move it
Slightly improved to handle if you cancel...
Sub MoveRows() Dim rngSource As Range, rngTarget As Range Const sPrompt$ = "Select the destination row position" Set rngSource = Selection.EntireRow Set rngTarget = Application.InputBox(Prompt:=sPrompt, _ Title:="Move Rows", Type:=8) If Not rngTarget Is Nothing Then rngSource.Cut rngTarget End Sub -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
DREAM FEATU A macro that moves a row but asks me where to move it
Is it possible to do just cells A:AP on the row where the current selection is? I seem to get errors deleting rows inside a formatted defined table.
On Wednesday, December 18, 2013 5:15:14 PM UTC-6, GS wrote: Slightly improved to handle if you cancel... Sub MoveRows() Dim rngSource As Range, rngTarget As Range Const sPrompt$ = "Select the destination row position" Set rngSource = Selection.EntireRow Set rngTarget = Application.InputBox(Prompt:=sPrompt, _ Title:="Move Rows", Type:=8) If Not rngTarget Is Nothing Then rngSource.Cut rngTarget End Sub -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
DREAM FEATU A macro that moves a row but asks me where to move it
Sweet. Thank you! I'm curious if it is possible to do the followong:
1. Just move cells A:AP (not the entire row) 2. Just move the values without formatting 3. Move the row I select as destination down instead of overwriting 4. Delete the empty space caused by the move On Wednesday, December 18, 2013 5:15:14 PM UTC-6, GS wrote: Slightly improved to handle if you cancel... Sub MoveRows() Dim rngSource As Range, rngTarget As Range Const sPrompt$ = "Select the destination row position" Set rngSource = Selection.EntireRow Set rngTarget = Application.InputBox(Prompt:=sPrompt, _ Title:="Move Rows", Type:=8) If Not rngTarget Is Nothing Then rngSource.Cut rngTarget End Sub -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
DREAM FEATU A macro that moves a row but asks me where to move it
Sweet. Thank you! I'm curious if it is possible to do the followong:
1. Just move cells A:AP (not the entire row) 2. Just move the values without formatting 3. Move the row I select as destination down instead of overwriting 4. Delete the empty space caused by the move Using 'Insert' can shift the destination cells down and put the source values in the resulting space. (No need to delete) This does nothing to the source cells... Sub CopySelection() Dim rngSource As Range, rngTarget As Range, lRow& Const sPrompt$ = "Select the destination row position" lRow = Selection.Row Set rngSource = ActiveSheet.Range("A" & lRow & ":AP" & lRow) Set rngTarget = Application.InputBox(Prompt:=sPrompt, _ Title:="Move Rows", Type:=8) If rngTarget Is Nothing Then Exit Sub With rngTarget.Resize(1, rngSource.Columns.Count) .Insert: .Offset(-1).Value = rngSource.Value End With End Sub -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
DREAM FEATU A macro that moves a row but asks me where to move it
Sweet again! So is there not a way to remove the rngSource from the sheet?
On Wednesday, December 18, 2013 7:28:04 PM UTC-6, GS wrote: Sweet. Thank you! I'm curious if it is possible to do the followong: 1. Just move cells A:AP (not the entire row) 2. Just move the values without formatting 3. Move the row I select as destination down instead of overwriting 4. Delete the empty space caused by the move Using 'Insert' can shift the destination cells down and put the source values in the resulting space. (No need to delete) This does nothing to the source cells... Sub CopySelection() Dim rngSource As Range, rngTarget As Range, lRow& Const sPrompt$ = "Select the destination row position" lRow = Selection.Row Set rngSource = ActiveSheet.Range("A" & lRow & ":AP" & lRow) Set rngTarget = Application.InputBox(Prompt:=sPrompt, _ Title:="Move Rows", Type:=8) If rngTarget Is Nothing Then Exit Sub With rngTarget.Resize(1, rngSource.Columns.Count) .Insert: .Offset(-1).Value = rngSource.Value End With End Sub -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
DREAM FEATU A macro that moves a row but asks me where to move it
Sweet again! So is there not a way to remove the rngSource from the
sheet? That's what the 1st macro did, but you said it was causing a problem. IMO, I don't see why you can't delete the row from the table. (assuming the table is rows/cols on the sheet, not a 'Table' object) -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
DREAM FEATU A macro that moves a row but asks me where to move it
The problem was that the 1st macro overwrote the selected destination rather than moved it.
I have added this line below, but it is deleting the row a couple down. Any clues? Selection.ListObject.ListRows(Selection.Row).Delet e On Wednesday, December 18, 2013 9:33:04 PM UTC-6, GS wrote: Sweet again! So is there not a way to remove the rngSource from the sheet? That's what the 1st macro did, but you said it was causing a problem. IMO, I don't see why you can't delete the row from the table. (assuming the table is rows/cols on the sheet, not a 'Table' object) -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
DREAM FEATU A macro that moves a row but asks me where to move it
I think I solved my own question. I changed the line to:
rngSource.EntireRow.Delete xlUp On Wednesday, December 18, 2013 10:30:14 PM UTC-6, magmike wrote: The problem was that the 1st macro overwrote the selected destination rather than moved it. I have added this line below, but it is deleting the row a couple down. Any clues? Selection.ListObject.ListRows(Selection.Row).Delet e On Wednesday, December 18, 2013 9:33:04 PM UTC-6, GS wrote: Sweet again! So is there not a way to remove the rngSource from the sheet? That's what the 1st macro did, but you said it was causing a problem. IMO, I don't see why you can't delete the row from the table. (assuming the table is rows/cols on the sheet, not a 'Table' object) -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
DREAM FEATU A macro that moves a row but asks me where to move it
I think I solved my own question. I changed the line to:
rngSource.EntireRow.Delete xlUp Yes, that's what I would do. Note, though, there is no need to specify the shift direction since you use the default. Sorry I wasn't clear on your explanation of the source row issue... Sub CopySelection() Dim rngSource As Range, rngTarget As Range, lRow& Const sPrompt$ = "Select the destination row position" lRow = Selection.Row Set rngSource = ActiveSheet.Range("A" & lRow & ":AP" & lRow) Set rngTarget = Application.InputBox(Prompt:=sPrompt, _ Title:="Move Rows", Type:=8) If rngTarget Is Nothing Then Exit Sub With rngTarget.Resize(1, rngSource.Columns.Count) .Insert: .Offset(-1).Value = rngSource.Value End With rngSource.EntireRow.Delete End Sub -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
DREAM FEATU A macro that moves a row but asks me where to move it
When I do t this way, it ends up deleting the new row. Any thoughts?
Dim rngSource As Range, rngTarget As Range Set rngSource = Selection.EntireRow rngSource.Copy Set rngTarget = Application.InputBox(Prompt:=sPrompt, Title:="Move Rows", Type:=8) If rngTarget Is Nothing Then Exit Sub If Not rngTarget Is Nothing Then rngSource.Cut rngTarget rngSource.EntireRow.Delete On Thursday, December 19, 2013 10:02:15 AM UTC-6, GS wrote: I think I solved my own question. I changed the line to: rngSource.EntireRow.Delete xlUp Yes, that's what I would do. Note, though, there is no need to specify the shift direction since you use the default. Sorry I wasn't clear on your explanation of the source row issue... Sub CopySelection() Dim rngSource As Range, rngTarget As Range, lRow& Const sPrompt$ = "Select the destination row position" lRow = Selection.Row Set rngSource = ActiveSheet.Range("A" & lRow & ":AP" & lRow) Set rngTarget = Application..InputBox(Prompt:=sPrompt, _ Title:="Move Rows", Type:=8) If rngTarget Is Nothing Then Exit Sub With rngTarget.Resize(1, rngSource.Columns.Count) .Insert: .Offset(-1).Value = rngSource.Value End With rngSource.EntireRow.Delete End Sub -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
DREAM FEATU A macro that moves a row but asks me where to move it
My last posted example deletes the source row, not the new row on the
target sheet. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
DREAM FEATU A macro that moves a row but asks me where to move it
Dim rngSource As Range, rngTarget As Range
Set rngSource = Selection.EntireRow rngSource.Copy Set rngTarget = Application.InputBox(Prompt:=sPrompt, Title:="Move Rows", Type:=8) If rngTarget Is Nothing Then Exit Sub If Not rngTarget Is Nothing Then rngSource.Cut rngTarget rngSource.EntireRow.Delete There's too many things wrong with this code and so I won't speak to that. Just use what I last posted... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
DREAM FEATU A macro that moves a row but asks me where to move it
On Wednesday, December 18, 2013 10:53:00 AM UTC-5, magmike wrote:
I would love to have a macro, that when a button is pushed, will move the selected row (or A:AP) and asked me where to move it. I don't know if a pop-up message box with buttons to push or a combo box with selections to click or another way is best, but based on the selections picked would move the row either to a new place on the current sheet or to another sheet. For example: "Potential" = sheet2 (inserted above row 3) "Future" = sheet3 (inserted above row 3) "Ageement Sent" = insert above row 3 on current sheet When the macro has completed, I would like the previous row not to be left empty but to be deleted. M I asking too much of Excel? Thanks in advance, magmike The List Assistant does exactly what you want: http://www.add-ins.com/list_assistant.htm |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
References that move down as formula moves across - and vice versa? | Excel Worksheet Functions | |||
References that move down as formula moves across - and vice versa? | Excel Programming | |||
Command button moves even with don't move or resize checked. | Excel Discussion (Misc queries) | |||
macro in 2003 template asks for non-existent XLS | Excel Programming | |||
Macro that asks for password | Excel Programming |