Im trying to learn from previous mistakes, which mainly seem to be around
not planning well enough ahead and designing code on the fly! So Im trying
to be pro-active �

Anyway, Im planning a workbook with three sheets: New, Active and Archive.
The sheets are basically the same with a row of headers at the top with work
details entered in rows beneath.

What I would like to do is have two buttons in the last row of the header
labelled €˜Active and €˜Archive €“ having the buttons in the header so that I
dont have to have a button in each row.

Then have a macro that can identify the active row and copy the entire.row
from €˜New to the first empty row in either €˜Active or €˜Archive depending
on which button is clicked. And then delete the old row from €˜New of course.

I have attempted to do this myself using bits of code from other solutions
you have been kind enough to give me here on the forum, but my knowledge of
vba isnt good enough to be able to unpick them (yet). But in the few weeks
that Ive been using vba I have learnt so much with your help!

Any help would be much appreciated.

Hi,

The code for both buttons would be the same apart from the destination sheet
name and I have included both in the code below (one commented out).

Put the buttions on your sheet and assin the code below to each

Private Sub CommandButton1_Click()
If Selection.Rows.Count 1 Then Exit Sub
Set sht = Sheets("Active")
'or
'Set sht = Sheets("Archive")
lastrow = sht.Cells(Cells.Rows.Count, "A").End(xlUp).Row
ActiveCell.EntireRow.Copy _
Destination:=sht.Range("A" & lastrow + 1)
ActiveCell.EntireRow.Delete
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"KevHardy" wrote:

Im trying to learn from previous mistakes, which mainly seem to be around
not planning well enough ahead and designing code on the fly! So Im trying
to be pro-active �

Anyway, Im planning a workbook with three sheets: New, Active and Archive.
The sheets are basically the same with a row of headers at the top with work
details entered in rows beneath.

What I would like to do is have two buttons in the last row of the header
labelled €˜Active and €˜Archive €“ having the buttons in the header so that I
dont have to have a button in each row.

Then have a macro that can identify the active row and copy the entire.row
from €˜New to the first empty row in either €˜Active or €˜Archive depending
on which button is clicked. And then delete the old row from €˜New of course.

I have attempted to do this myself using bits of code from other solutions
you have been kind enough to give me here on the forum, but my knowledge of
vba isnt good enough to be able to unpick them (yet). But in the few weeks
that Ive been using vba I have learnt so much with your help!

Any help would be much appreciated.

Hi

I think it is something like this you want:

Sub CopyActive()
Dim DestCell As Range
Set DestCell = Worhskeets("Active").Range("A1").End(xlDown).Offse t(1, 0)
ActiveCell.EntireRow.Copy Destination:=DestCell
ActiveCell.EntireRow.Delete Shift:=xlShiftUp
End Sub

Regards,
Per

"KevHardy" skrev i meddelelsen
...
Im trying to learn from previous mistakes, which mainly seem to be around
not planning well enough ahead and designing code on the fly! So Im
trying
to be pro-active �

Anyway, Im planning a workbook with three sheets: New, Active and
Archive.
The sheets are basically the same with a row of headers at the top with
work
details entered in rows beneath.

What I would like to do is have two buttons in the last row of the header
labelled €˜Active and €˜Archive €“ having the buttons in the header so that
I
dont have to have a button in each row.

Then have a macro that can identify the active row and copy the entire.row
from €˜New to the first empty row in either €˜Active or €˜Archive
depending
on which button is clicked. And then delete the old row from €˜New of
course.

I have attempted to do this myself using bits of code from other solutions
you have been kind enough to give me here on the forum, but my knowledge
of
vba isnt good enough to be able to unpick them (yet). But in the few
weeks
that Ive been using vba I have learnt so much with your help!

Any help would be much appreciated.

Thanks Mike. Nice bit of code!

"Mike H" wrote:

Hi,

The code for both buttons would be the same apart from the destination sheet
name and I have included both in the code below (one commented out).

Put the buttions on your sheet and assin the code below to each

Private Sub CommandButton1_Click()
If Selection.Rows.Count 1 Then Exit Sub
Set sht = Sheets("Active")
'or
'Set sht = Sheets("Archive")
lastrow = sht.Cells(Cells.Rows.Count, "A").End(xlUp).Row
ActiveCell.EntireRow.Copy _
Destination:=sht.Range("A" & lastrow + 1)
ActiveCell.EntireRow.Delete
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"KevHardy" wrote:

Im trying to learn from previous mistakes, which mainly seem to be around
not planning well enough ahead and designing code on the fly! So Im trying
to be pro-active �

Anyway, Im planning a workbook with three sheets: New, Active and Archive.
The sheets are basically the same with a row of headers at the top with work
details entered in rows beneath.

What I would like to do is have two buttons in the last row of the header
labelled €˜Active and €˜Archive €“ having the buttons in the header so that I
dont have to have a button in each row.

Then have a macro that can identify the active row and copy the entire.row
from €˜New to the first empty row in either €˜Active or €˜Archive depending
on which button is clicked. And then delete the old row from €˜New of course.

I have attempted to do this myself using bits of code from other solutions
you have been kind enough to give me here on the forum, but my knowledge of
vba isnt good enough to be able to unpick them (yet). But in the few weeks
that Ive been using vba I have learnt so much with your help!

Any help would be much appreciated.

Glad I could help and thanks for the feedback
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"KevHardy" wrote:

Thanks Mike. Nice bit of code!

"Mike H" wrote:

Hi,

The code for both buttons would be the same apart from the destination sheet
name and I have included both in the code below (one commented out).

Put the buttions on your sheet and assin the code below to each

Private Sub CommandButton1_Click()
If Selection.Rows.Count 1 Then Exit Sub
Set sht = Sheets("Active")
'or
'Set sht = Sheets("Archive")
lastrow = sht.Cells(Cells.Rows.Count, "A").End(xlUp).Row
ActiveCell.EntireRow.Copy _
Destination:=sht.Range("A" & lastrow + 1)
ActiveCell.EntireRow.Delete
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"KevHardy" wrote:

Im trying to learn from previous mistakes, which mainly seem to be around
not planning well enough ahead and designing code on the fly! So Im trying
to be pro-active �

Anyway, Im planning a workbook with three sheets: New, Active and Archive.
The sheets are basically the same with a row of headers at the top with work
details entered in rows beneath.

What I would like to do is have two buttons in the last row of the header
labelled €˜Active and €˜Archive €“ having the buttons in the header so that I
dont have to have a button in each row.

Then have a macro that can identify the active row and copy the entire.row
from €˜New to the first empty row in either €˜Active or €˜Archive depending
on which button is clicked. And then delete the old row from €˜New of course.

I have attempted to do this myself using bits of code from other solutions
you have been kind enough to give me here on the forum, but my knowledge of
vba isnt good enough to be able to unpick them (yet). But in the few weeks
that Ive been using vba I have learnt so much with your help!

Any help would be much appreciated.