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

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
>

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

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

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

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
>

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

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

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

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

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

I'm sorry... I'm not very good at vba, and dont know a whole lot about it. I
dont quite understand what you mean by adding one... Are you saying that the
code needs to look like this?

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

"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
> > > >

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

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