Button to move row and delete old row
 

I've created a button at the top of a spreadsheet that says "Move Row to
Tracking Tab". When this button is clicked, I want it to move the current
row (where their cursor is), to the Tracking Tab, and add it either at the
top of the spreadsheet (under the header row) or to the very bottom, while at
the same time, it deletes the old row from the first sheet entirely. The
first cell that contains data on the Tracking Tab is A4. Everything above
that is the header. (And just in case you need to know, the first tab is
titled "Western", and they are the only 2 tabs in the workbook) I've tried
the following code, but cant get it to work out right. It's giving me fits
with the line "Range("A4").Select", and I'm not really sure why.

Here is the code I used:

Private Sub MoveRow_Click()
ActiveCell.EntireRow.Copy
Worksheets("Tracking").Activate 'the other sheet
Range("A4").Select
ActiveCell.Insert Shift:=xlShiftDown
Worksheets("Western").Activate 'back to the original
ActiveCell.EntireRow.Delete Shift:=xlShiftUp
End Sub

Button to move row and delete old row
 

before pasting do not use range("a4").select
try

Rows("4:4").Select
Selection.Insert Shift:=xlDown

"Stacie Fugate" wrote:

I've created a button at the top of a spreadsheet that says "Move Row to
Tracking Tab". When this button is clicked, I want it to move the current
row (where their cursor is), to the Tracking Tab, and add it either at the
top of the spreadsheet (under the header row) or to the very bottom, while at
the same time, it deletes the old row from the first sheet entirely. The
first cell that contains data on the Tracking Tab is A4. Everything above
that is the header. (And just in case you need to know, the first tab is
titled "Western", and they are the only 2 tabs in the workbook) I've tried
the following code, but cant get it to work out right. It's giving me fits
with the line "Range("A4").Select", and I'm not really sure why.

Here is the code I used:

Private Sub MoveRow_Click()
ActiveCell.EntireRow.Copy
Worksheets("Tracking").Activate 'the other sheet
Range("A4").Select
ActiveCell.Insert Shift:=xlShiftDown
Worksheets("Western").Activate 'back to the original
ActiveCell.EntireRow.Delete Shift:=xlShiftUp
End Sub

Button to move row and delete old row
 

Its still not working... Any suggestions? When I use that, it takes me back
to the debugger and highlights the Rows("4:4").Select line... I really dont
know what's wrong.

"ben" wrote:

before pasting do not use range("a4").select
try

Rows("4:4").Select
Selection.Insert Shift:=xlDown

"Stacie Fugate" wrote:

I've created a button at the top of a spreadsheet that says "Move Row to
Tracking Tab". When this button is clicked, I want it to move the current
row (where their cursor is), to the Tracking Tab, and add it either at the
top of the spreadsheet (under the header row) or to the very bottom, while at
the same time, it deletes the old row from the first sheet entirely. The
first cell that contains data on the Tracking Tab is A4. Everything above
that is the header. (And just in case you need to know, the first tab is
titled "Western", and they are the only 2 tabs in the workbook) I've tried
the following code, but cant get it to work out right. It's giving me fits
with the line "Range("A4").Select", and I'm not really sure why.

Here is the code I used:

Private Sub MoveRow_Click()
ActiveCell.EntireRow.Copy
Worksheets("Tracking").Activate 'the other sheet
Range("A4").Select
ActiveCell.Insert Shift:=xlShiftDown
Worksheets("Western").Activate 'back to the original
ActiveCell.EntireRow.Delete Shift:=xlShiftUp
End Sub

Button to move row and delete old row
 

i'm sorry i think i just realized what is wrong
use activesheet.rows("4:4").select

i run into this problem a lot


"Stacie Fugate" wrote:

Its still not working... Any suggestions? When I use that, it takes me back
to the debugger and highlights the Rows("4:4").Select line... I really dont
know what's wrong.

"ben" wrote:

before pasting do not use range("a4").select
try

Rows("4:4").Select
Selection.Insert Shift:=xlDown

"Stacie Fugate" wrote:

I've created a button at the top of a spreadsheet that says "Move Row to
Tracking Tab". When this button is clicked, I want it to move the current
row (where their cursor is), to the Tracking Tab, and add it either at the
top of the spreadsheet (under the header row) or to the very bottom, while at
the same time, it deletes the old row from the first sheet entirely. The
first cell that contains data on the Tracking Tab is A4. Everything above
that is the header. (And just in case you need to know, the first tab is
titled "Western", and they are the only 2 tabs in the workbook) I've tried
the following code, but cant get it to work out right. It's giving me fits
with the line "Range("A4").Select", and I'm not really sure why.

Here is the code I used:

Private Sub MoveRow_Click()
ActiveCell.EntireRow.Copy
Worksheets("Tracking").Activate 'the other sheet
Range("A4").Select
ActiveCell.Insert Shift:=xlShiftDown
Worksheets("Western").Activate 'back to the original
ActiveCell.EntireRow.Delete Shift:=xlShiftUp
End Sub

Button to move row and delete old row
 

it's possible either that the workbook is protected. check for worksheet
protection also try rows("4") and what is the exact error message you get


"Stacie Fugate" wrote:

Its still not working... Any suggestions? When I use that, it takes me back
to the debugger and highlights the Rows("4:4").Select line... I really dont
know what's wrong.

"ben" wrote:

before pasting do not use range("a4").select
try

Rows("4:4").Select
Selection.Insert Shift:=xlDown

"Stacie Fugate" wrote:

I've created a button at the top of a spreadsheet that says "Move Row to
Tracking Tab". When this button is clicked, I want it to move the current
row (where their cursor is), to the Tracking Tab, and add it either at the
top of the spreadsheet (under the header row) or to the very bottom, while at
the same time, it deletes the old row from the first sheet entirely. The
first cell that contains data on the Tracking Tab is A4. Everything above
that is the header. (And just in case you need to know, the first tab is
titled "Western", and they are the only 2 tabs in the workbook) I've tried
the following code, but cant get it to work out right. It's giving me fits
with the line "Range("A4").Select", and I'm not really sure why.

Here is the code I used:

Private Sub MoveRow_Click()
ActiveCell.EntireRow.Copy
Worksheets("Tracking").Activate 'the other sheet
Range("A4").Select
ActiveCell.Insert Shift:=xlShiftDown
Worksheets("Western").Activate 'back to the original
ActiveCell.EntireRow.Delete Shift:=xlShiftUp
End Sub

Button to move row and delete old row
 

sometimes excel has no clue what sheet to do things coded even though it
seems clear to coder... :)

try put activesheet before that range line like this:

ActiveSheet.Range("A4").Select

This should work...

"Stacie Fugate" wrote:

I've created a button at the top of a spreadsheet that says "Move Row to
Tracking Tab". When this button is clicked, I want it to move the current
row (where their cursor is), to the Tracking Tab, and add it either at the
top of the spreadsheet (under the header row) or to the very bottom, while at
the same time, it deletes the old row from the first sheet entirely. The
first cell that contains data on the Tracking Tab is A4. Everything above
that is the header. (And just in case you need to know, the first tab is
titled "Western", and they are the only 2 tabs in the workbook) I've tried
the following code, but cant get it to work out right. It's giving me fits
with the line "Range("A4").Select", and I'm not really sure why.

Here is the code I used:

Private Sub MoveRow_Click()
ActiveCell.EntireRow.Copy
Worksheets("Tracking").Activate 'the other sheet
Range("A4").Select
ActiveCell.Insert Shift:=xlShiftDown
Worksheets("Western").Activate 'back to the original
ActiveCell.EntireRow.Delete Shift:=xlShiftUp
End Sub

Button to move row and delete old row
 

So then, the code would be:

Private Sub MoveRow_Click()
ActiveCell.EntireRow.Copy
Worksheets("Tracking").Activate 'the other sheet
activesheet.rows("4:4").select
Selection.Insert Shift:=xlDown
Worksheets("Western").Activate 'back to the original
ActiveCell.EntireRow.Delete Shift:=xlShiftUp
End Sub



"ben" wrote:

i'm sorry i think i just realized what is wrong
use activesheet.rows("4:4").select

i run into this problem a lot


"Stacie Fugate" wrote:

Its still not working... Any suggestions? When I use that, it takes me back
to the debugger and highlights the Rows("4:4").Select line... I really dont
know what's wrong.

"ben" wrote:

before pasting do not use range("a4").select
try

Rows("4:4").Select
Selection.Insert Shift:=xlDown

"Stacie Fugate" wrote:

I've created a button at the top of a spreadsheet that says "Move Row to
Tracking Tab". When this button is clicked, I want it to move the current
row (where their cursor is), to the Tracking Tab, and add it either at the
top of the spreadsheet (under the header row) or to the very bottom, while at
the same time, it deletes the old row from the first sheet entirely. The
first cell that contains data on the Tracking Tab is A4. Everything above
that is the header. (And just in case you need to know, the first tab is
titled "Western", and they are the only 2 tabs in the workbook) I've tried
the following code, but cant get it to work out right. It's giving me fits
with the line "Range("A4").Select", and I'm not really sure why.

Here is the code I used:

Private Sub MoveRow_Click()
ActiveCell.EntireRow.Copy
Worksheets("Tracking").Activate 'the other sheet
Range("A4").Select
ActiveCell.Insert Shift:=xlShiftDown
Worksheets("Western").Activate 'back to the original
ActiveCell.EntireRow.Delete Shift:=xlShiftUp
End Sub

Button to move row and delete old row
 

Okay, that worked great! But, one problem.. how do I get it to insert the
row instead of replacing what was currently in that row? Instead of
replacing the row that was there, I just need it to add the new row to that
list... either at the top or at the bottom. Thing of it is, the bottom row
number varies constantly, it could have 5 rows, it could have 60 rows... how
do I get it to know which row to go to?

Additionally, I have 3 different sections on my spreadsheet, how can I get
excel to recognize if a row is under Section 1, then move it to the next
available empty row in Section 1 on the Tracking Tab? If my cursor is on a
row in Section 2, then move this row to the next empty row in Section 2 on
the Tracking Tab.

Below is kinda an example of what my spreadsheet looks like. Both tabs look
identical, the only difference is the data under the column header...

WELL TYPE 1 INFORMATION
Smith #1 County ST 1/13/05
Jones #2 County ST 1/14/05

WELL TYPE 2 INFORMATION
Brown #3 County ST 1/16/05

WELL TYPE 3 INFORMATION
Haynes #6 County ST 1/17/05


"nippetee" wrote:

sometimes excel has no clue what sheet to do things coded even though it
seems clear to coder... :)

try put activesheet before that range line like this:

ActiveSheet.Range("A4").Select

This should work...

"Stacie Fugate" wrote:

I've created a button at the top of a spreadsheet that says "Move Row to
Tracking Tab". When this button is clicked, I want it to move the current
row (where their cursor is), to the Tracking Tab, and add it either at the
top of the spreadsheet (under the header row) or to the very bottom, while at
the same time, it deletes the old row from the first sheet entirely. The
first cell that contains data on the Tracking Tab is A4. Everything above
that is the header. (And just in case you need to know, the first tab is
titled "Western", and they are the only 2 tabs in the workbook) I've tried
the following code, but cant get it to work out right. It's giving me fits
with the line "Range("A4").Select", and I'm not really sure why.

Here is the code I used:

Private Sub MoveRow_Click()
ActiveCell.EntireRow.Copy
Worksheets("Tracking").Activate 'the other sheet
Range("A4").Select
ActiveCell.Insert Shift:=xlShiftDown
Worksheets("Western").Activate 'back to the original
ActiveCell.EntireRow.Delete Shift:=xlShiftUp
End Sub

Button to move row and delete old row
 

Also, is there a way to get it to still do this with the top 3 rows being
locked when the sheet is proctected? The way it is set up is that the users
cannot modify the header (the top 3 rows) any, they cannot add any columns,
etc..., they can only add and modify data below the first 3 rows. When I
protected the sheet again, the button didnt work, and it gave me the
debugger... any clues why it does that?

"nippetee" wrote:

sometimes excel has no clue what sheet to do things coded even though it
seems clear to coder... :)

try put activesheet before that range line like this:

ActiveSheet.Range("A4").Select

This should work...

"Stacie Fugate" wrote:

I've created a button at the top of a spreadsheet that says "Move Row to
Tracking Tab". When this button is clicked, I want it to move the current
row (where their cursor is), to the Tracking Tab, and add it either at the
top of the spreadsheet (under the header row) or to the very bottom, while at
the same time, it deletes the old row from the first sheet entirely. The
first cell that contains data on the Tracking Tab is A4. Everything above
that is the header. (And just in case you need to know, the first tab is
titled "Western", and they are the only 2 tabs in the workbook) I've tried
the following code, but cant get it to work out right. It's giving me fits
with the line "Range("A4").Select", and I'm not really sure why.

Here is the code I used:

Private Sub MoveRow_Click()
ActiveCell.EntireRow.Copy
Worksheets("Tracking").Activate 'the other sheet
Range("A4").Select
ActiveCell.Insert Shift:=xlShiftDown
Worksheets("Western").Activate 'back to the original
ActiveCell.EntireRow.Delete Shift:=xlShiftUp
End Sub

Button to move row and delete old row
 

try going back to

activesheet.range("a4").select
ActiveCell.Insert Shift:=xlShiftDown
activesheet.paste

as for inserting to the bottom

ActiveSheet.UsedRange.Rows.Count

gives you the last row with data in it
adding one to that will give you the next row down
just select that and paste




"Stacie Fugate" wrote:

Okay, that worked great! But, one problem.. how do I get it to insert the
row instead of replacing what was currently in that row? Instead of
replacing the row that was there, I just need it to add the new row to that
list... either at the top or at the bottom. Thing of it is, the bottom row
number varies constantly, it could have 5 rows, it could have 60 rows... how
do I get it to know which row to go to?

Additionally, I have 3 different sections on my spreadsheet, how can I get
excel to recognize if a row is under Section 1, then move it to the next
available empty row in Section 1 on the Tracking Tab? If my cursor is on a
row in Section 2, then move this row to the next empty row in Section 2 on
the Tracking Tab.

Below is kinda an example of what my spreadsheet looks like. Both tabs look
identical, the only difference is the data under the column header...

WELL TYPE 1 INFORMATION
Smith #1 County ST 1/13/05
Jones #2 County ST 1/14/05

WELL TYPE 2 INFORMATION
Brown #3 County ST 1/16/05

WELL TYPE 3 INFORMATION
Haynes #6 County ST 1/17/05


"nippetee" wrote:

sometimes excel has no clue what sheet to do things coded even though it
seems clear to coder... :)

try put activesheet before that range line like this:

ActiveSheet.Range("A4").Select

This should work...

"Stacie Fugate" wrote:

I've created a button at the top of a spreadsheet that says "Move Row to
Tracking Tab". When this button is clicked, I want it to move the current
row (where their cursor is), to the Tracking Tab, and add it either at the
top of the spreadsheet (under the header row) or to the very bottom, while at
the same time, it deletes the old row from the first sheet entirely. The
first cell that contains data on the Tracking Tab is A4. Everything above
that is the header. (And just in case you need to know, the first tab is
titled "Western", and they are the only 2 tabs in the workbook) I've tried
the following code, but cant get it to work out right. It's giving me fits
with the line "Range("A4").Select", and I'm not really sure why.

Here is the code I used:

Private Sub MoveRow_Click()
ActiveCell.EntireRow.Copy
Worksheets("Tracking").Activate 'the other sheet
Range("A4").Select
ActiveCell.Insert Shift:=xlShiftDown
Worksheets("Western").Activate 'back to the original
ActiveCell.EntireRow.Delete Shift:=xlShiftUp
End Sub

Button to move row and delete old row
 

yes, if the worksheet is protected you can not add or change much
under vba help index search protect, unprotect, you can do it
programmatically then turn it back on when you are finished with your adding
rows

to find the last row
use

activesheet.usedrange.rows.count

adding one to that will give you the first avialable empty row

as for adding to top
try first inserting a row
selecting the inserted row and then pasting your data to the blank row

"Stacie Fugate" wrote:

Also, is there a way to get it to still do this with the top 3 rows being
locked when the sheet is proctected? The way it is set up is that the users
cannot modify the header (the top 3 rows) any, they cannot add any columns,
etc..., they can only add and modify data below the first 3 rows. When I
protected the sheet again, the button didnt work, and it gave me the
debugger... any clues why it does that?

"nippetee" wrote:

sometimes excel has no clue what sheet to do things coded even though it
seems clear to coder... :)

try put activesheet before that range line like this:

ActiveSheet.Range("A4").Select

This should work...

"Stacie Fugate" wrote:

I've created a button at the top of a spreadsheet that says "Move Row to
Tracking Tab". When this button is clicked, I want it to move the current
row (where their cursor is), to the Tracking Tab, and add it either at the
top of the spreadsheet (under the header row) or to the very bottom, while at
the same time, it deletes the old row from the first sheet entirely. The
first cell that contains data on the Tracking Tab is A4. Everything above
that is the header. (And just in case you need to know, the first tab is
titled "Western", and they are the only 2 tabs in the workbook) I've tried
the following code, but cant get it to work out right. It's giving me fits
with the line "Range("A4").Select", and I'm not really sure why.

Here is the code I used:

Private Sub MoveRow_Click()
ActiveCell.EntireRow.Copy
Worksheets("Tracking").Activate 'the other sheet
Range("A4").Select
ActiveCell.Insert Shift:=xlShiftDown
Worksheets("Western").Activate 'back to the original
ActiveCell.EntireRow.Delete Shift:=xlShiftUp
End Sub

Button to move row and delete old row
 

Okay... I've worked on this and got everything so far working perfectly.
I've created the button and when it's clicked it will move the current row to
the appropriate tab and insert it there instead of replacing it. It also
deletes the old row. Now, for my next question regarding this though.

My spreadsheet is divided up into 3 sections. For the sake of simplicity,
I'll just call them Well Type 1, Well Type 2 and Well Type 3. They are laid
out like this:

WELL TYPE 1 INFORMATION
Smith #1 County ST 1/13/05
Jones #2 County ST 1/14/05

WELL TYPE 2 INFORMATION
Brown #3 County ST 1/16/05

WELL TYPE 3 INFORMATION
Haynes #6 County ST 1/17/05

Currently, under the last row of information under each section, there is an
add button that when clicked will add a new row (formatted as necessary with
borders and background cell color) under the specified section. My workbook
has 2 tabs, Western, and Tracking. Both of these tabs look exactly the same
(just like shown above), the only difference is the data that exists in each
section.

My question is this: now that I've got it set up to move the rows and
delete the old row, and it's working fine, how do I make excel move it to the
right section? If they click on a cell that falls under Well Type 2, and hit
the Move Row to Tracking Tab button, how can I make excel know to move it
under the Well Type 2 section of the Tracking Tab? How can it look for a
section title and know to insert it under that section?


"ben" wrote:

yes, if the worksheet is protected you can not add or change much
under vba help index search protect, unprotect, you can do it
programmatically then turn it back on when you are finished with your adding
rows

to find the last row
use

activesheet.usedrange.rows.count

adding one to that will give you the first avialable empty row

as for adding to top
try first inserting a row
selecting the inserted row and then pasting your data to the blank row

"Stacie Fugate" wrote:

Also, is there a way to get it to still do this with the top 3 rows being
locked when the sheet is proctected? The way it is set up is that the users
cannot modify the header (the top 3 rows) any, they cannot add any columns,
etc..., they can only add and modify data below the first 3 rows. When I
protected the sheet again, the button didnt work, and it gave me the
debugger... any clues why it does that?

"nippetee" wrote:

sometimes excel has no clue what sheet to do things coded even though it
seems clear to coder... :)

try put activesheet before that range line like this:

ActiveSheet.Range("A4").Select

This should work...

"Stacie Fugate" wrote:

I've created a button at the top of a spreadsheet that says "Move Row to
Tracking Tab". When this button is clicked, I want it to move the current
row (where their cursor is), to the Tracking Tab, and add it either at the
top of the spreadsheet (under the header row) or to the very bottom, while at
the same time, it deletes the old row from the first sheet entirely. The
first cell that contains data on the Tracking Tab is A4. Everything above
that is the header. (And just in case you need to know, the first tab is
titled "Western", and they are the only 2 tabs in the workbook) I've tried
the following code, but cant get it to work out right. It's giving me fits
with the line "Range("A4").Select", and I'm not really sure why.

Here is the code I used:

Private Sub MoveRow_Click()
ActiveCell.EntireRow.Copy
Worksheets("Tracking").Activate 'the other sheet
Range("A4").Select
ActiveCell.Insert Shift:=xlShiftDown
Worksheets("Western").Activate 'back to the original
ActiveCell.EntireRow.Delete Shift:=xlShiftUp
End Sub

Button to move row and delete old row
 

do you know what a named range is?

under each of the sections ON BOTH PAGES select the first empty cell in
column1
look to the otp left of excel and you will see something like R234C1 or
A234. Click into that box and type a name for that cell
eg
westernwell1
then
under well type 2
westernwell2
under type 3
westernwell3

and for the tracking sheet
select the first empty cell column 1 under each section
trackingwell1

trackingwell2

trackingwell3
etc
for more help in named ranges use excel index help.

now no matter how you arrange and re-arrange the cells those "names" will
always exist
to place under the appropriate row you can use
rowtoinset = range("trackingwell1").row
this will return the first available range below your well #1 on your
tracking page
this will allow you to find where to place it
now as to finding where it came from
well1, well2, or well3. If you have a button under each one, use that
specific buttons code to identify which one it came from and then use the
rowtoinset = range("use the correct name for tracking WELL here").row
to specify where to place it




"Stacie Fugate" wrote:

Okay... I've worked on this and got everything so far working perfectly.
I've created the button and when it's clicked it will move the current row to
the appropriate tab and insert it there instead of replacing it. It also
deletes the old row. Now, for my next question regarding this though.

My spreadsheet is divided up into 3 sections. For the sake of simplicity,
I'll just call them Well Type 1, Well Type 2 and Well Type 3. They are laid
out like this:

WELL TYPE 1 INFORMATION
Smith #1 County ST 1/13/05
Jones #2 County ST 1/14/05

WELL TYPE 2 INFORMATION
Brown #3 County ST 1/16/05

WELL TYPE 3 INFORMATION
Haynes #6 County ST 1/17/05

Currently, under the last row of information under each section, there is an
add button that when clicked will add a new row (formatted as necessary with
borders and background cell color) under the specified section. My workbook
has 2 tabs, Western, and Tracking. Both of these tabs look exactly the same
(just like shown above), the only difference is the data that exists in each
section.

My question is this: now that I've got it set up to move the rows and
delete the old row, and it's working fine, how do I make excel move it to the
right section? If they click on a cell that falls under Well Type 2, and hit
the Move Row to Tracking Tab button, how can I make excel know to move it
under the Well Type 2 section of the Tracking Tab? How can it look for a
section title and know to insert it under that section?


"ben" wrote:

yes, if the worksheet is protected you can not add or change much
under vba help index search protect, unprotect, you can do it
programmatically then turn it back on when you are finished with your adding
rows

to find the last row
use

activesheet.usedrange.rows.count

adding one to that will give you the first avialable empty row

as for adding to top
try first inserting a row
selecting the inserted row and then pasting your data to the blank row

"Stacie Fugate" wrote:

Also, is there a way to get it to still do this with the top 3 rows being
locked when the sheet is proctected? The way it is set up is that the users
cannot modify the header (the top 3 rows) any, they cannot add any columns,
etc..., they can only add and modify data below the first 3 rows. When I
protected the sheet again, the button didnt work, and it gave me the
debugger... any clues why it does that?

"nippetee" wrote:

sometimes excel has no clue what sheet to do things coded even though it
seems clear to coder... :)

try put activesheet before that range line like this:

ActiveSheet.Range("A4").Select

This should work...

"Stacie Fugate" wrote:

I've created a button at the top of a spreadsheet that says "Move Row to
Tracking Tab". When this button is clicked, I want it to move the current
row (where their cursor is), to the Tracking Tab, and add it either at the
top of the spreadsheet (under the header row) or to the very bottom, while at
the same time, it deletes the old row from the first sheet entirely. The
first cell that contains data on the Tracking Tab is A4. Everything above
that is the header. (And just in case you need to know, the first tab is
titled "Western", and they are the only 2 tabs in the workbook) I've tried
the following code, but cant get it to work out right. It's giving me fits
with the line "Range("A4").Select", and I'm not really sure why.

Here is the code I used:

Private Sub MoveRow_Click()
ActiveCell.EntireRow.Copy
Worksheets("Tracking").Activate 'the other sheet
Range("A4").Select
ActiveCell.Insert Shift:=xlShiftDown
Worksheets("Western").Activate 'back to the original
ActiveCell.EntireRow.Delete Shift:=xlShiftUp
End Sub