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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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