OK, so you're down to a workbook with a single worksheet in it, correct?

It depends on what you mean by "protect the worksheet" and "messed with"?

If you by "messed with" you mean that you want the workbook to open up the
next time with the sheet unfiltered, then I believe that this is simple
enough to get the job done.

This is Workbook event related code. It goes into the 'ThisWorkbook'
object's code module: Press [Alt]+[F11] to open the VB Editor and look in
the 'Project - VBAProject' window for the 'ThisWorkbook' object. It will be
in the list of "Microsoft Excel Objects" which you may have to expand. If
you don't even see the Project - VBAProject window, press [Ctrl]+[R] to bring
it into view.

OK - double-click on the 'ThisWorkbook' object and copy the code below and
paste it into the module. From then on, when the workbook is closed, if the
"master" sheet is auto-filtered, the auto-filtering will be turned off.
Change "master" to whatever you really name that worksheet.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ThisWorkbook.Worksheets("master").FilterMode Then
'the sheet is AutoFiltered - turn that off
ThisWorkbook.Worksheets("master").Range("A1").Auto Filter
End If
End Sub


"L.S." wrote:

Thanks for your response.
What we've decided, at this point, to do is create one worksheet with 6
columns; Doc#, Category, Date of Doc., Box#, Folder#, Description, then
for those doing research on a specific Category they can use AutoFilter.
They can filter out everything except the Doc, Image or object locations
that they want.
Next question is how to protect the worksheet so when someone is done and
closes out of Excel, the original worksheet is not "messed" with?

We're going to keep this as simple as possible as we're trying to purchase
Past Perfect software for museums.


"JLatham" wrote in message
...
Worksheet functions cannot 'physically' copy rows or columns, nor can they
format, hide, display, delete or add rows/columns.

Your question included "copy and move", so that definitely rules out
worksheet functions. To me, the "move" means to copy the data to another
location and delete it from the original location.

A macro could easily perform the task.

Sub MoveByCategory()
Dim masterSheet As Worksheet
Dim catSheet As Worksheet
Dim masterList As Range
Dim anyMasterListEntry As Range
Dim destinationNextRow As Long

Set masterSheet = ThisWorkbook.Worksheets("master")
'start at row 2, assumes labels in row 1
Set masterList = masterSheet.Range("B2:" & _
masterSheet.Range("B" & Rows.Count).End(xlUp).Address)
'this section performs the copying
For Each anyMasterListEntry In masterList
Set catSheet = ThisWorkbook.Worksheets(anyMasterListEntry.Value)
destinationNextRow = catSheet.Range("B" & _
Rows.Count).End(xlUp).Offset(1, 0).Row
'copy columns A:E on current row to next available
'row on the appropriate sheet
masterSheet.Range("A" & anyMasterListEntry.Row & ":" & _
"E" & anyMasterListEntry.Row).Copy _
catSheet.Range("A" & destinationNextRow)
Next
Application.CutCopyMode = False
'now delete the original data on the 'master' sheet
masterList.Rows.EntireRow.Delete
'finally, a little housekeeping
Set masterList = Nothing
Set masterSheet = Nothing
Set catSheet = Nothing
End Sub

To put the code into your workbook, open (a copy! just in case) and press
[Alt]+[F11] to open the VB Editor. In the VBE, choose Insert -- Module
and
copy and paste the code above into it. Close the VBE. You can run the
macro
from
Tools -- Macro -- Macros (pre 2007)
or from the Developer tab in 2007 (if the Developer tab is not visible,
use
the Office Button, then [Excel Options] button and in the Popular group,
check the box next to "Show Developer Tab in the Ribbon".
"L.S." wrote:

Creating a SS with 6 tabs(master,A,B,C,D,E) each having 5
columns(doc#,category,date,box,folder).
"Category" can have 5 different values, A,B,C,D,E

According to which value in 'category' is entered in master I want to
copy
the row and move it to corresponding tab.

Can an IF statement do this?

thanks,
L.


.



.