Manage a long tag list

petro62

New Member
Joined
Jul 15, 2013
Messages
14
So I "manage" a decent size list of tags or assets. A limited number of people have access to the file, but all of them can add new tags to it. There are couple few functions I was hoping to set up to help prevent it from getting messed up (format wise or otherwise). Currently the sheet has no macros or anything on it and is currently just under 7000 items long.

Item 1:
Currently there are 11 fields in each row that they can input data into. 8 of which are required. Without bogging down the system I was curious the best way to have them enter this information would be. Right now they have to insert a row and then put in all the info. Would there be a better way to have them hit a "Add New" and it goes through some sort of dialog box for entering their information and then it automatically inserts the new row and tag in alphabetical order?

Item 2:
Items on this tag list can become obsolete. We have another tab for obsolete items, but it requires the user to manually copy it over and often the original is left on the main list. Would it be possible to move the obsolete tag to the new the obsoleted tab automatically? So the user puts Y or Yes in the cell for obsolete could it pop up a message that they have to confirm and then based on that it copies that obsolete over alphabetically to the obsolete tab and then hides and or deletes that information off the original tab?

Item 3:
Is there a way that every time the file is closed or saved that it reverts back to a certain filtering/sort?

1617894047741.png
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

rpaulson

Well-known Member
Joined
Oct 4, 2007
Messages
1,170
#1 - I would create a simple user form. You can have it check to insure the user entered data in the appropriate fields and then when the click the ADD button it would write a new record to your database.

#2- Code below

VBA Code:
Sub Move_obsolete()

Dim rs As Worksheet
Set rs = Worksheets("Obsolete sheet")

For r = Range("H" & Rows.Count).End(xlUp).Row To 1 Step -1

If UCase(Left(Cells(r, "H"), 1)) = "Y" Then 'move to obsolete sheet and delete
    lr = rs.Range("A" & Rows.Count).End(xlUp).Row + 1
    Rows(r).EntireRow.Copy Destination:=rs.Range("A" & lr)
    Rows(r).EntireRow.Delete
End If

Next r

End Sub

#3 - I would sort your list when the workbook is opened.

VBA Code:
Private Sub Workbook_Open()

Worksheets("Main sheet").Activate
lr = rs.Range("A" & Rows.Count).End(xlUp).Row + 1
Range("A1:K" & lr).Sort Key1:=Range("A1"), Header:=xlYes 'Order1:=xlDescending
End Sub

hth,
Ross
 

petro62

New Member
Joined
Jul 15, 2013
Messages
14
These worked great. Thanks for the help and putting the code out there for me.
 

petro62

New Member
Joined
Jul 15, 2013
Messages
14
#1 - I would create a simple user form. You can have it check to insure the user entered data in the appropriate fields and then when the click the ADD button it would write a new record to your database.

#2- Code below

VBA Code:
Sub Move_obsolete()

Dim rs As Worksheet
Set rs = Worksheets("Obsolete sheet")

For r = Range("H" & Rows.Count).End(xlUp).Row To 1 Step -1

If UCase(Left(Cells(r, "H"), 1)) = "Y" Then 'move to obsolete sheet and delete
    lr = rs.Range("A" & Rows.Count).End(xlUp).Row + 1
    Rows(r).EntireRow.Copy Destination:=rs.Range("A" & lr)
    Rows(r).EntireRow.Delete
End If

Next r

End Sub

#3 - I would sort your list when the workbook is opened.

VBA Code:
Private Sub Workbook_Open()

Worksheets("Main sheet").Activate
lr = rs.Range("A" & Rows.Count).End(xlUp).Row + 1
Range("A1:K" & lr).Sort Key1:=Range("A1"), Header:=xlYes 'Order1:=xlDescending
End Sub

hth,
Ross
One last question. On the row I am moving from the main sheet to obsolete I want to change the date updated cell to reflect the day it was moved over to obsolete. How would I go about that?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,905
Messages
5,638,911
Members
417,058
Latest member
BRYCEPIETROWIAK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top