DREAM FEATURE: A macro that moves a row but asks me where to move it

M

magmike

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 clickor 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
 
G

GS

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
Click to expand...

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
 
G

GS

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
 
M

magmike

I am not a programmer. I am smart enough to modify code for my own uses andeven 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 isthere or insert it above the current A3:AP3?

How would I predefine 3 or 4 options, and cause the prompt that comes up togive me a choice of those?

 
G

GS

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?
Click to expand...

Ah! In this case a turnkey solution is needed, then. If I correctly
understand your task then...

1. select the row 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 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
 
G

GS

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
 
M

magmike

Is it possible to do just cells A:AP on the row where the current selectionis? I seem to get errors deleting rows inside a formatted defined table.
 
M

magmike

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
 
G

GS

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
Click to expand...


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
 
G

GS

Sweet again! So is there not a way to remove the rngSource from the
sheet?
Click to expand...

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
 
M

magmike

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. Anyclues?

Selection.ListObject.ListRows(Selection.Row).Delete

 
M

magmike

I think I solved my own question. I changed the line to:

rngSource.EntireRow.Delete xlUp
 
G

GS

I think I solved my own question. I changed the line to:
rngSource.EntireRow.Delete xlUp
Click to expand...

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
 
M

magmike

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

 
G

GS

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
Click to expand...

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
 
B

bobflanagan1

I would love to have a macro, that when a button is pushed, will move theselected 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
Click to expand...

The List Assistant does exactly what you want:

http://www.add-ins.com/list_assistant.htm
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Move row to another location on sheet 4
Insert Row also adds formulas and moves cursor 2
Need macro to move cell contents from one sheet to another 1
Find a row and move it to the end 2
if an element in a row is true then copy whole row to another shee 13
Macro Help 6
Inserting a Copied Row inside a table 6
Macro to Delete Row based on <3 8

Top