Excel database (list): button to move a record (row) to next sheet which is a similar list (several problems)

[ketilwaa]

I use Excel to keep track over clients, and the sheets include lists, so that I can easily hide and show the records I need. (I used this tutorial to make the lists: http://spreadsheets.about.com/od/datamanagementinexcel/ss/excel_database_6.htm)

(Unfortunately the document is on a secure zone, so I can not copy/paste to the forum.)

The first sheet are active clients, and the second are inactive clients.

Problem 1:
I made macros (found online and slightly adapted) to move clients from the active to the inactive list. The macros are as follows:

[COLOR=#0000ff]Sub[/COLOR] MoveActiveRow() 
 
    Application.ScreenUpdating = [COLOR=#0000ff]False[/COLOR] 
 
    [COLOR=#0000ff]Dim[/COLOR] strSheetName, strCellAddress [COLOR=#0000ff]As[/COLOR] [COLOR=#0000ff]String[/COLOR] 
    strSheetName = ActiveSheet.Name 
    strCellAddress = ActiveCell.Address([COLOR=#0000ff]False[/COLOR], [COLOR=#0000ff]False[/COLOR]) 
 
    Rows(ActiveCell.Row).Cut 
    Sheets("Sheet3").Select [COLOR=#006400]'Change sheet name to whatever consolidated tab name is.[/COLOR]
    Range("A" & Range("A65536").End(xlUp).Row).Select 
    ActiveSheet.Paste 
    Application.CutCopyMode = [COLOR=#0000ff]False[/COLOR] 
 
    Range("A" & ActiveCell.Row).Select 
 
    Sheets(strSheetName).Select 
    Range(strCellAddress).Select 
 
    Rows(ActiveCell.Row).Delete 
 
    Application.ScreenUpdating = [COLOR=#0000ff]True[/COLOR] 
 
[COLOR=#0000ff]End Sub[/COLOR]

I then made a button on each sheet move the active row to the other sheet.
I thought it worked, but I now see that if I have existing records in the list I want to move the row to, it overwrites it. That is a huge no-no, of course.
Can anyone tell what I've done wrong.

Problem 2:
Some of the cells in the list use validation: values are choosen from a list. However, often the drop down list does not appear when I'm punching in a new record. I'm not sure what I did here either. The workaround might be to set up validation for the entire columns, but I think that might pose a problem further down the road.

Hope someone can help!

 

[VoG]

Try

 Rows(ActiveCell.Row).Cut Destination:=Sheets("Sheet3").Range("A" & Range("A" & Rows.Count).End(xlUp).Row).Offset(1)

 

[ketilwaa]

Do you mean like so:

[COLOR=#0000ff]Sub[/COLOR] MoveActiveRow() 
 
    Application.ScreenUpdating = [COLOR=#0000ff]False[/COLOR] 
 
    [COLOR=#0000ff]Dim[/COLOR] strSheetName, strCellAddress [COLOR=#0000ff]As[/COLOR] [COLOR=#0000ff]String[/COLOR] 
    strSheetName = ActiveSheet.Name 
    strCellAddress = ActiveCell.Address([COLOR=#0000ff]False[/COLOR], [COLOR=#0000ff]False[/COLOR]) 

 Rows(ActiveCell.Row).Cut Destination:=Sheets("Sheet3").Range("A" & Range("A" & Rows.Count).End(xlUp).Row).Offset(1) 
 

' commenting out    Rows(ActiveCell.Row).Cut 
' commenting out    Sheets("Sheet3").Select [COLOR=#006400]'Change sheet name to whatever consolidated tab name is.[/COLOR]
' commenting out    Range("A" & Range("A65536").End(xlUp).Row).Select 

    ActiveSheet.Paste 
    Application.CutCopyMode = [COLOR=#0000ff]False[/COLOR] 
 
    Range("A" & ActiveCell.Row).Select 
 
    Sheets(strSheetName).Select 
    Range(strCellAddress).Select 
 
    Rows(ActiveCell.Row).Delete 
 
    Application.ScreenUpdating = [COLOR=#0000ff]True[/COLOR] 
 
[COLOR=#0000ff]End Sub[/COLOR]

(Assuming the ":" after Destination in your code was a typo.)

Sorry if I'm being stupid here, this is my first attempt at macros for 15 years... Anyways, I'm getting a "Invalid use of property", highlighting ".Offset"

 

[ketilwaa]

Just adding, I just tried with my original macro sveeral more times, and it doesn't overwrite every time, which makes this even scarier: the prospect of losing data is there, and it just happens sometimes... I need to make dead sure that existing rows are not deleted!

 

[VoG]

Sorry, I had a typo. This is all you need - tested and working

Sub MoveActiveRow()
Application.ScreenUpdating = False
Rows(ActiveCell.Row).Cut Destination:=Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1)
Rows(ActiveCell.Row).Delete
Application.ScreenUpdating = True
End Sub

 

[ketilwaa]

Thanks, that seems to work. Hope it works *every* single time
My first use of this forum, great to get help so soon!