I've got a spreadsheet that has 2 tabs on it. Both tabs are protected, with
the header (top 3 rows) being locked, and the rest is all unlocked. I have a
button on the top of the form that when clicked, it will move the current row
to the 2nd tab, in A4 (the row right under the header). This works fine,
except when the 2nd row is protected. Is there any way to keep the 2nd sheet
protected, but still let it move the row, either to A4, or to the next
available line, whether it be A10, or A30, or whatever? Is there a way to
make it look for the next blank line and move it there? Or is there a way to
let it move to A4 when the sheet is protected? My current code behind the
Move Row button is below:

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

I need to make sure that the new row being moved over does not replace the
current row, but just moves it all down a row, or something similar so that
all the new stuff is there. I hope someone can help me... I'm desperate...

Thanks!
Stacie

Stacie,

To ensure that no data is overwritten, replace this line in your code:

ActiveSheet.Range("A5").Select

with

do until activesheet.range("a5").offset(intRowCounter)=""
intRowCounter=intRowCounter+1
loop
ActiveSheet.Range("a5").offset(intRowCounter).sele ct

This will look for the first available blank cell running down column A,
starting in row 5. You could also use a variant on the UsedRange method.

http://HelpExcel.com

"Stacie Fugate" wrote:

I've got a spreadsheet that has 2 tabs on it. Both tabs are protected, with
the header (top 3 rows) being locked, and the rest is all unlocked. I have a
button on the top of the form that when clicked, it will move the current row
to the 2nd tab, in A4 (the row right under the header). This works fine,
except when the 2nd row is protected. Is there any way to keep the 2nd sheet
protected, but still let it move the row, either to A4, or to the next
available line, whether it be A10, or A30, or whatever? Is there a way to
make it look for the next blank line and move it there? Or is there a way to
let it move to A4 when the sheet is protected? My current code behind the
Move Row button is below:

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

I need to make sure that the new row being moved over does not replace the
current row, but just moves it all down a row, or something similar so that
all the new stuff is there. I hope someone can help me... I'm desperate...

Thanks!
Stacie

If I replace that one line of code with what you gave me, I get the following
error message:

Run time error '1004'
Insert method of Range class failed

and then when I go into the debugger, it has this row highlighted:

ActiveCell.Insert Shift:=xlShiftDown

Below is what code I had in there when I tried the method you mentioned:

Private Sub MoveRow_Click()
ActiveCell.EntireRow.Copy
Worksheets("Tracking").Activate 'the other sheet
Do Until ActiveSheet.Range("a5").Offset(intRowCounter) = ""
intRowCounter = intRowCounter + 1
Loop
ActiveSheet.Range("a5").Offset(intRowCounter).Sele ct

ActiveCell.Insert Shift:=xlShiftDown
Worksheets("Western").Activate 'back to the original
ActiveCell.EntireRow.Delete Shift:=xlShiftUp
End Sub

Is this because the worksheets are both protected? Both sheets are
protected, meaning that the top 3 rows are locked, and the rest of the sheet
is open for editing. When I protected the sheet, I checked every box with
the exception of these four: Insert columns; Delete columns; Select locked
cells; Format columns. I hope you can help me with this... I'm really
desperate.. haha..

Thanks again for your help,
Stacie

"galimi" wrote:

Stacie,

To ensure that no data is overwritten, replace this line in your code:

ActiveSheet.Range("A5").Select

with

do until activesheet.range("a5").offset(intRowCounter)=""
intRowCounter=intRowCounter+1
loop
ActiveSheet.Range("a5").offset(intRowCounter).sele ct

This will look for the first available blank cell running down column A,
starting in row 5. You could also use a variant on the UsedRange method.

http://HelpExcel.com

"Stacie Fugate" wrote:

I've got a spreadsheet that has 2 tabs on it. Both tabs are protected, with
the header (top 3 rows) being locked, and the rest is all unlocked. I have a
button on the top of the form that when clicked, it will move the current row
to the 2nd tab, in A4 (the row right under the header). This works fine,
except when the 2nd row is protected. Is there any way to keep the 2nd sheet
protected, but still let it move the row, either to A4, or to the next
available line, whether it be A10, or A30, or whatever? Is there a way to
make it look for the next blank line and move it there? Or is there a way to
let it move to A4 when the sheet is protected? My current code behind the
Move Row button is below:

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

I need to make sure that the new row being moved over does not replace the
current row, but just moves it all down a row, or something similar so that
all the new stuff is there. I hope someone can help me... I'm desperate...

Thanks!
Stacie

Stacie,

Try turning off all protection to see if that is the issue.

http://HelpExcel.com


"Stacie Fugate" wrote:

If I replace that one line of code with what you gave me, I get the following
error message:

Run time error '1004'
Insert method of Range class failed

and then when I go into the debugger, it has this row highlighted:

ActiveCell.Insert Shift:=xlShiftDown

Below is what code I had in there when I tried the method you mentioned:

Private Sub MoveRow_Click()
ActiveCell.EntireRow.Copy
Worksheets("Tracking").Activate 'the other sheet
Do Until ActiveSheet.Range("a5").Offset(intRowCounter) = ""
intRowCounter = intRowCounter + 1
Loop
ActiveSheet.Range("a5").Offset(intRowCounter).Sele ct

ActiveCell.Insert Shift:=xlShiftDown
Worksheets("Western").Activate 'back to the original
ActiveCell.EntireRow.Delete Shift:=xlShiftUp
End Sub

Is this because the worksheets are both protected? Both sheets are
protected, meaning that the top 3 rows are locked, and the rest of the sheet
is open for editing. When I protected the sheet, I checked every box with
the exception of these four: Insert columns; Delete columns; Select locked
cells; Format columns. I hope you can help me with this... I'm really
desperate.. haha..

Thanks again for your help,
Stacie

"galimi" wrote:

Stacie,

To ensure that no data is overwritten, replace this line in your code:

ActiveSheet.Range("A5").Select

with

do until activesheet.range("a5").offset(intRowCounter)=""
intRowCounter=intRowCounter+1
loop
ActiveSheet.Range("a5").offset(intRowCounter).sele ct

This will look for the first available blank cell running down column A,
starting in row 5. You could also use a variant on the UsedRange method.

http://HelpExcel.com

"Stacie Fugate" wrote:

I've got a spreadsheet that has 2 tabs on it. Both tabs are protected, with
the header (top 3 rows) being locked, and the rest is all unlocked. I have a
button on the top of the form that when clicked, it will move the current row
to the 2nd tab, in A4 (the row right under the header). This works fine,
except when the 2nd row is protected. Is there any way to keep the 2nd sheet
protected, but still let it move the row, either to A4, or to the next
available line, whether it be A10, or A30, or whatever? Is there a way to
make it look for the next blank line and move it there? Or is there a way to
let it move to A4 when the sheet is protected? My current code behind the
Move Row button is below:

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

I need to make sure that the new row being moved over does not replace the
current row, but just moves it all down a row, or something similar so that
all the new stuff is there. I hope someone can help me... I'm desperate...

Thanks!
Stacie

Yes, that is the problem.. When I unprotect the 2nd sheet, it works fine, by
adding the row at the very bottom. However, for this spreadsheet, both
sheets must remain protected. How can I fix this???

"galimi" wrote:

Stacie,

Try turning off all protection to see if that is the issue.

http://HelpExcel.com


"Stacie Fugate" wrote:

If I replace that one line of code with what you gave me, I get the following
error message:

Run time error '1004'
Insert method of Range class failed

and then when I go into the debugger, it has this row highlighted:

ActiveCell.Insert Shift:=xlShiftDown

Below is what code I had in there when I tried the method you mentioned:

Private Sub MoveRow_Click()
ActiveCell.EntireRow.Copy
Worksheets("Tracking").Activate 'the other sheet
Do Until ActiveSheet.Range("a5").Offset(intRowCounter) = ""
intRowCounter = intRowCounter + 1
Loop
ActiveSheet.Range("a5").Offset(intRowCounter).Sele ct

ActiveCell.Insert Shift:=xlShiftDown
Worksheets("Western").Activate 'back to the original
ActiveCell.EntireRow.Delete Shift:=xlShiftUp
End Sub

Is this because the worksheets are both protected? Both sheets are
protected, meaning that the top 3 rows are locked, and the rest of the sheet
is open for editing. When I protected the sheet, I checked every box with
the exception of these four: Insert columns; Delete columns; Select locked
cells; Format columns. I hope you can help me with this... I'm really
desperate.. haha..

Thanks again for your help,
Stacie

"galimi" wrote:

Stacie,

To ensure that no data is overwritten, replace this line in your code:

ActiveSheet.Range("A5").Select

with

do until activesheet.range("a5").offset(intRowCounter)=""
intRowCounter=intRowCounter+1
loop
ActiveSheet.Range("a5").offset(intRowCounter).sele ct

This will look for the first available blank cell running down column A,
starting in row 5. You could also use a variant on the UsedRange method.

http://HelpExcel.com

"Stacie Fugate" wrote:

I've got a spreadsheet that has 2 tabs on it. Both tabs are protected, with
the header (top 3 rows) being locked, and the rest is all unlocked. I have a
button on the top of the form that when clicked, it will move the current row
to the 2nd tab, in A4 (the row right under the header). This works fine,
except when the 2nd row is protected. Is there any way to keep the 2nd sheet
protected, but still let it move the row, either to A4, or to the next
available line, whether it be A10, or A30, or whatever? Is there a way to
make it look for the next blank line and move it there? Or is there a way to
let it move to A4 when the sheet is protected? My current code behind the
Move Row button is below:

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

I need to make sure that the new row being moved over does not replace the
current row, but just moves it all down a row, or something similar so that
all the new stuff is there. I hope someone can help me... I'm desperate...

Thanks!
Stacie

Before the insertion of the cells, we can programatically unprotect the sheet
with a line similar to the following

ActiveSheet.unprotect

I'd change ActiveSheet to represent the sheet object being referenced, e.g.,
Sheet2

After the copy occurs, we can re-protect the sheet with a line similar to
the following

ActiveSheet.protect DrawingObjects:=True, Contents:=True, Scenarios:=True

Again, change the ActiveSheet to the sheet object being referenced, e.g.,
Sheet2

http://HelpExcel.com
"Stacie Fugate" wrote:

Yes, that is the problem.. When I unprotect the 2nd sheet, it works fine, by
adding the row at the very bottom. However, for this spreadsheet, both
sheets must remain protected. How can I fix this???

"galimi" wrote:

Stacie,

Try turning off all protection to see if that is the issue.

http://HelpExcel.com


"Stacie Fugate" wrote:

If I replace that one line of code with what you gave me, I get the following
error message:

Run time error '1004'
Insert method of Range class failed

and then when I go into the debugger, it has this row highlighted:

ActiveCell.Insert Shift:=xlShiftDown

Below is what code I had in there when I tried the method you mentioned:

Private Sub MoveRow_Click()
ActiveCell.EntireRow.Copy
Worksheets("Tracking").Activate 'the other sheet
Do Until ActiveSheet.Range("a5").Offset(intRowCounter) = ""
intRowCounter = intRowCounter + 1
Loop
ActiveSheet.Range("a5").Offset(intRowCounter).Sele ct

ActiveCell.Insert Shift:=xlShiftDown
Worksheets("Western").Activate 'back to the original
ActiveCell.EntireRow.Delete Shift:=xlShiftUp
End Sub

Is this because the worksheets are both protected? Both sheets are
protected, meaning that the top 3 rows are locked, and the rest of the sheet
is open for editing. When I protected the sheet, I checked every box with
the exception of these four: Insert columns; Delete columns; Select locked
cells; Format columns. I hope you can help me with this... I'm really
desperate.. haha..

Thanks again for your help,
Stacie

"galimi" wrote:

Stacie,

To ensure that no data is overwritten, replace this line in your code:

ActiveSheet.Range("A5").Select

with

do until activesheet.range("a5").offset(intRowCounter)=""
intRowCounter=intRowCounter+1
loop
ActiveSheet.Range("a5").offset(intRowCounter).sele ct

This will look for the first available blank cell running down column A,
starting in row 5. You could also use a variant on the UsedRange method.

http://HelpExcel.com

"Stacie Fugate" wrote:

I've got a spreadsheet that has 2 tabs on it. Both tabs are protected, with
the header (top 3 rows) being locked, and the rest is all unlocked. I have a
button on the top of the form that when clicked, it will move the current row
to the 2nd tab, in A4 (the row right under the header). This works fine,
except when the 2nd row is protected. Is there any way to keep the 2nd sheet
protected, but still let it move the row, either to A4, or to the next
available line, whether it be A10, or A30, or whatever? Is there a way to
make it look for the next blank line and move it there? Or is there a way to
let it move to A4 when the sheet is protected? My current code behind the
Move Row button is below:

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

I need to make sure that the new row being moved over does not replace the
current row, but just moves it all down a row, or something similar so that
all the new stuff is there. I hope someone can help me... I'm desperate...

Thanks!
Stacie

So then... my code should read (note that the name of my 2nd sheet is
"Tracking")? Is this right????? (sorry for the million questions, I'm not
that great with vb).

Private Sub MoveRow_Click()
ActiveCell.EntireRow.Copy
Worksheets("Tracking").Activate 'the other sheet
Do Until ActiveSheet.Range("a5").Offset(intRowCounter) = ""
intRowCounter = intRowCounter + 1
Loop
ActiveSheet.Range("a5").Offset(intRowCounter).Sele ct
Worksheets("Tracking").unprotect
ActiveCell.Insert Shift:=xlShiftDown
Worksheets("Tracking").protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Worksheets("Western").Activate 'back to the original
ActiveCell.EntireRow.Delete Shift:=xlShiftUp
End Sub


"galimi" wrote:

Before the insertion of the cells, we can programatically unprotect the sheet
with a line similar to the following

ActiveSheet.unprotect

I'd change ActiveSheet to represent the sheet object being referenced, e.g.,
Sheet2

After the copy occurs, we can re-protect the sheet with a line similar to
the following

ActiveSheet.protect DrawingObjects:=True, Contents:=True, Scenarios:=True

Again, change the ActiveSheet to the sheet object being referenced, e.g.,
Sheet2

http://HelpExcel.com
"Stacie Fugate" wrote:

Yes, that is the problem.. When I unprotect the 2nd sheet, it works fine, by
adding the row at the very bottom. However, for this spreadsheet, both
sheets must remain protected. How can I fix this???

"galimi" wrote:

Stacie,

Try turning off all protection to see if that is the issue.

http://HelpExcel.com


"Stacie Fugate" wrote:

If I replace that one line of code with what you gave me, I get the following
error message:

Run time error '1004'
Insert method of Range class failed

and then when I go into the debugger, it has this row highlighted:

ActiveCell.Insert Shift:=xlShiftDown

Below is what code I had in there when I tried the method you mentioned:

Private Sub MoveRow_Click()
ActiveCell.EntireRow.Copy
Worksheets("Tracking").Activate 'the other sheet
Do Until ActiveSheet.Range("a5").Offset(intRowCounter) = ""
intRowCounter = intRowCounter + 1
Loop
ActiveSheet.Range("a5").Offset(intRowCounter).Sele ct

ActiveCell.Insert Shift:=xlShiftDown
Worksheets("Western").Activate 'back to the original
ActiveCell.EntireRow.Delete Shift:=xlShiftUp
End Sub

Is this because the worksheets are both protected? Both sheets are
protected, meaning that the top 3 rows are locked, and the rest of the sheet
is open for editing. When I protected the sheet, I checked every box with
the exception of these four: Insert columns; Delete columns; Select locked
cells; Format columns. I hope you can help me with this... I'm really
desperate.. haha..

Thanks again for your help,
Stacie

"galimi" wrote:

Stacie,

To ensure that no data is overwritten, replace this line in your code:

ActiveSheet.Range("A5").Select

with

do until activesheet.range("a5").offset(intRowCounter)=""
intRowCounter=intRowCounter+1
loop
ActiveSheet.Range("a5").offset(intRowCounter).sele ct

This will look for the first available blank cell running down column A,
starting in row 5. You could also use a variant on the UsedRange method.

http://HelpExcel.com

"Stacie Fugate" wrote:

I've got a spreadsheet that has 2 tabs on it. Both tabs are protected, with
the header (top 3 rows) being locked, and the rest is all unlocked. I have a
button on the top of the form that when clicked, it will move the current row
to the 2nd tab, in A4 (the row right under the header). This works fine,
except when the 2nd row is protected. Is there any way to keep the 2nd sheet
protected, but still let it move the row, either to A4, or to the next
available line, whether it be A10, or A30, or whatever? Is there a way to
make it look for the next blank line and move it there? Or is there a way to
let it move to A4 when the sheet is protected? My current code behind the
Move Row button is below:

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

I need to make sure that the new row being moved over does not replace the
current row, but just moves it all down a row, or something similar so that
all the new stuff is there. I hope someone can help me... I'm desperate...

Thanks!
Stacie

When I try to put in that code from my last message, it doesnt give me an
error message, it acts like it does it, but when I go to the Tracking tab,
the data is gone, and its not on the first tab either... its just... gone...
haha

"galimi" wrote:

Before the insertion of the cells, we can programatically unprotect the sheet
with a line similar to the following

ActiveSheet.unprotect

I'd change ActiveSheet to represent the sheet object being referenced, e.g.,
Sheet2

After the copy occurs, we can re-protect the sheet with a line similar to
the following

ActiveSheet.protect DrawingObjects:=True, Contents:=True, Scenarios:=True

Again, change the ActiveSheet to the sheet object being referenced, e.g.,
Sheet2

http://HelpExcel.com
"Stacie Fugate" wrote:

Yes, that is the problem.. When I unprotect the 2nd sheet, it works fine, by
adding the row at the very bottom. However, for this spreadsheet, both
sheets must remain protected. How can I fix this???

"galimi" wrote:

Stacie,

Try turning off all protection to see if that is the issue.

http://HelpExcel.com


"Stacie Fugate" wrote:

If I replace that one line of code with what you gave me, I get the following
error message:

Run time error '1004'
Insert method of Range class failed

and then when I go into the debugger, it has this row highlighted:

ActiveCell.Insert Shift:=xlShiftDown

Below is what code I had in there when I tried the method you mentioned:

Private Sub MoveRow_Click()
ActiveCell.EntireRow.Copy
Worksheets("Tracking").Activate 'the other sheet
Do Until ActiveSheet.Range("a5").Offset(intRowCounter) = ""
intRowCounter = intRowCounter + 1
Loop
ActiveSheet.Range("a5").Offset(intRowCounter).Sele ct

ActiveCell.Insert Shift:=xlShiftDown
Worksheets("Western").Activate 'back to the original
ActiveCell.EntireRow.Delete Shift:=xlShiftUp
End Sub

Is this because the worksheets are both protected? Both sheets are
protected, meaning that the top 3 rows are locked, and the rest of the sheet
is open for editing. When I protected the sheet, I checked every box with
the exception of these four: Insert columns; Delete columns; Select locked
cells; Format columns. I hope you can help me with this... I'm really
desperate.. haha..

Thanks again for your help,
Stacie

"galimi" wrote:

Stacie,

To ensure that no data is overwritten, replace this line in your code:

ActiveSheet.Range("A5").Select

with

do until activesheet.range("a5").offset(intRowCounter)=""
intRowCounter=intRowCounter+1
loop
ActiveSheet.Range("a5").offset(intRowCounter).sele ct

This will look for the first available blank cell running down column A,
starting in row 5. You could also use a variant on the UsedRange method.

http://HelpExcel.com

"Stacie Fugate" wrote:

I've got a spreadsheet that has 2 tabs on it. Both tabs are protected, with
the header (top 3 rows) being locked, and the rest is all unlocked. I have a
button on the top of the form that when clicked, it will move the current row
to the 2nd tab, in A4 (the row right under the header). This works fine,
except when the 2nd row is protected. Is there any way to keep the 2nd sheet
protected, but still let it move the row, either to A4, or to the next
available line, whether it be A10, or A30, or whatever? Is there a way to
make it look for the next blank line and move it there? Or is there a way to
let it move to A4 when the sheet is protected? My current code behind the
Move Row button is below:

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

I need to make sure that the new row being moved over does not replace the
current row, but just moves it all down a row, or something similar so that
all the new stuff is there. I hope someone can help me... I'm desperate...

Thanks!
Stacie

Stacie,

I have placed an example in

http://Galimi.com/Examples/Stacie.xls

The only code necessary is as follows

Sub moverow()

Rows(ActiveCell.Row).Copy
Sheet2.Unprotect

Sheet2.Rows(Sheet2.UsedRange.Rows.Count + 1).PasteSpecial xlPasteValues
Sheet2.Protect
End Sub

http://HelpExcel.com

"Stacie Fugate" wrote:

When I try to put in that code from my last message, it doesnt give me an
error message, it acts like it does it, but when I go to the Tracking tab,
the data is gone, and its not on the first tab either... its just... gone...
haha

"galimi" wrote:

Before the insertion of the cells, we can programatically unprotect the sheet
with a line similar to the following

ActiveSheet.unprotect

I'd change ActiveSheet to represent the sheet object being referenced, e.g.,
Sheet2

After the copy occurs, we can re-protect the sheet with a line similar to
the following

ActiveSheet.protect DrawingObjects:=True, Contents:=True, Scenarios:=True

Again, change the ActiveSheet to the sheet object being referenced, e.g.,
Sheet2

http://HelpExcel.com
"Stacie Fugate" wrote:

Yes, that is the problem.. When I unprotect the 2nd sheet, it works fine, by
adding the row at the very bottom. However, for this spreadsheet, both
sheets must remain protected. How can I fix this???

"galimi" wrote:

Stacie,

Try turning off all protection to see if that is the issue.

http://HelpExcel.com


"Stacie Fugate" wrote:

If I replace that one line of code with what you gave me, I get the following
error message:

Run time error '1004'
Insert method of Range class failed

and then when I go into the debugger, it has this row highlighted:

ActiveCell.Insert Shift:=xlShiftDown

Below is what code I had in there when I tried the method you mentioned:

Private Sub MoveRow_Click()
ActiveCell.EntireRow.Copy
Worksheets("Tracking").Activate 'the other sheet
Do Until ActiveSheet.Range("a5").Offset(intRowCounter) = ""
intRowCounter = intRowCounter + 1
Loop
ActiveSheet.Range("a5").Offset(intRowCounter).Sele ct

ActiveCell.Insert Shift:=xlShiftDown
Worksheets("Western").Activate 'back to the original
ActiveCell.EntireRow.Delete Shift:=xlShiftUp
End Sub

Is this because the worksheets are both protected? Both sheets are
protected, meaning that the top 3 rows are locked, and the rest of the sheet
is open for editing. When I protected the sheet, I checked every box with
the exception of these four: Insert columns; Delete columns; Select locked
cells; Format columns. I hope you can help me with this... I'm really
desperate.. haha..

Thanks again for your help,
Stacie

"galimi" wrote:

Stacie,

To ensure that no data is overwritten, replace this line in your code:

ActiveSheet.Range("A5").Select

with

do until activesheet.range("a5").offset(intRowCounter)=""
intRowCounter=intRowCounter+1
loop
ActiveSheet.Range("a5").offset(intRowCounter).sele ct

This will look for the first available blank cell running down column A,
starting in row 5. You could also use a variant on the UsedRange method.

http://HelpExcel.com

"Stacie Fugate" wrote:

I've got a spreadsheet that has 2 tabs on it. Both tabs are protected, with
the header (top 3 rows) being locked, and the rest is all unlocked. I have a
button on the top of the form that when clicked, it will move the current row
to the 2nd tab, in A4 (the row right under the header). This works fine,
except when the 2nd row is protected. Is there any way to keep the 2nd sheet
protected, but still let it move the row, either to A4, or to the next
available line, whether it be A10, or A30, or whatever? Is there a way to
make it look for the next blank line and move it there? Or is there a way to
let it move to A4 when the sheet is protected? My current code behind the
Move Row button is below:

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

I need to make sure that the new row being moved over does not replace the
current row, but just moves it all down a row, or something similar so that
all the new stuff is there. I hope someone can help me... I'm desperate...

Thanks!
Stacie

Sigh... its still not working. Here is a link to the file if you want to
take a look at it:

http://members.cox.net/stacie.fugate...rangements.xls

Also, if you'd like to email me, my email address is ,
or you may communicate with me through msn messenger using the email address
.

Additionally, the sheet you sent to me didn't work either... I dont know
what I'm missing here.

Thanks again for all your hard work,
Stacie Fugate

"galimi" wrote:

Stacie,

I have placed an example in

http://Galimi.com/Examples/Stacie.xls

The only code necessary is as follows

Sub moverow()

Rows(ActiveCell.Row).Copy
Sheet2.Unprotect

Sheet2.Rows(Sheet2.UsedRange.Rows.Count + 1).PasteSpecial xlPasteValues
Sheet2.Protect
End Sub

http://HelpExcel.com

"Stacie Fugate" wrote:

When I try to put in that code from my last message, it doesnt give me an
error message, it acts like it does it, but when I go to the Tracking tab,
the data is gone, and its not on the first tab either... its just... gone...
haha

"galimi" wrote:

Before the insertion of the cells, we can programatically unprotect the sheet
with a line similar to the following

ActiveSheet.unprotect

I'd change ActiveSheet to represent the sheet object being referenced, e.g.,
Sheet2

After the copy occurs, we can re-protect the sheet with a line similar to
the following

ActiveSheet.protect DrawingObjects:=True, Contents:=True, Scenarios:=True

Again, change the ActiveSheet to the sheet object being referenced, e.g.,
Sheet2

http://HelpExcel.com
"Stacie Fugate" wrote:

Yes, that is the problem.. When I unprotect the 2nd sheet, it works fine, by
adding the row at the very bottom. However, for this spreadsheet, both
sheets must remain protected. How can I fix this???

"galimi" wrote:

Stacie,

Try turning off all protection to see if that is the issue.

http://HelpExcel.com


"Stacie Fugate" wrote:

If I replace that one line of code with what you gave me, I get the following
error message:

Run time error '1004'
Insert method of Range class failed

and then when I go into the debugger, it has this row highlighted:

ActiveCell.Insert Shift:=xlShiftDown

Below is what code I had in there when I tried the method you mentioned:

Private Sub MoveRow_Click()
ActiveCell.EntireRow.Copy
Worksheets("Tracking").Activate 'the other sheet
Do Until ActiveSheet.Range("a5").Offset(intRowCounter) = ""
intRowCounter = intRowCounter + 1
Loop
ActiveSheet.Range("a5").Offset(intRowCounter).Sele ct

ActiveCell.Insert Shift:=xlShiftDown
Worksheets("Western").Activate 'back to the original
ActiveCell.EntireRow.Delete Shift:=xlShiftUp
End Sub

Is this because the worksheets are both protected? Both sheets are
protected, meaning that the top 3 rows are locked, and the rest of the sheet
is open for editing. When I protected the sheet, I checked every box with
the exception of these four: Insert columns; Delete columns; Select locked
cells; Format columns. I hope you can help me with this... I'm really
desperate.. haha..

Thanks again for your help,
Stacie

"galimi" wrote:

Stacie,

To ensure that no data is overwritten, replace this line in your code:

ActiveSheet.Range("A5").Select

with

do until activesheet.range("a5").offset(intRowCounter)=""
intRowCounter=intRowCounter+1
loop
ActiveSheet.Range("a5").offset(intRowCounter).sele ct

This will look for the first available blank cell running down column A,
starting in row 5. You could also use a variant on the UsedRange method.

http://HelpExcel.com

"Stacie Fugate" wrote:

I've got a spreadsheet that has 2 tabs on it. Both tabs are protected, with
the header (top 3 rows) being locked, and the rest is all unlocked. I have a
button on the top of the form that when clicked, it will move the current row
to the 2nd tab, in A4 (the row right under the header). This works fine,
except when the 2nd row is protected. Is there any way to keep the 2nd sheet
protected, but still let it move the row, either to A4, or to the next
available line, whether it be A10, or A30, or whatever? Is there a way to
make it look for the next blank line and move it there? Or is there a way to
let it move to A4 when the sheet is protected? My current code behind the
Move Row button is below:

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

I need to make sure that the new row being moved over does not replace the
current row, but just moves it all down a row, or something similar so that
all the new stuff is there. I hope someone can help me... I'm desperate...

Thanks!
Stacie