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.

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.
>
>
> .
>

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" <(E-Mail Removed)> wrote in message
news:96585F63-FA99-4836-9663-(E-Mail Removed)...
> 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.
>>
>>
>> .
>>

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").AutoFilter
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" <(E-Mail Removed)> wrote in message
> news:96585F63-FA99-4836-9663-(E-Mail Removed)...
> > 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.
> >>
> >>
> >> .
> >>
>
>
> .
>

Correct on your question about one worksheet but will use your formula to
set the workbook back to unfiltered BUT another thing we want to do is
protect the worksheet/workbook from someone changing the data.
We want people to be able to use the Sort and/or AutoFilter function to get
the info they need BUT we don't want them to be able to change the
data(delete and add info) to the work!!!!!.

Is this possible?


"JLatham" <(E-Mail Removed)> wrote in message
news74EDE6C-3BFB-4B27-91CE-(E-Mail Removed)...
> 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").AutoFilter
> 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" <(E-Mail Removed)> wrote in message
>> news:96585F63-FA99-4836-9663-(E-Mail Removed)...
>> > 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.
>> >>
>> >>
>> >> .
>> >>
>>
>>
>> .
>>

Try pre-applying the autofilter, then protect* the sheet with a password
Check: Use Autofilter in the Protect Sheet dialog**
*via Tools > Protection > Protect Sheet
**there's also a "Sort" option in the dialog to check, but I couldn't get it
to work
--
Max
Singapore

"L.S." <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
.... BUT another thing we want to do is
> protect the worksheet/workbook from someone changing the data.
> We want people to be able to use the Sort and/or AutoFilter function to
> get the info they need BUT we don't want them to be able to change the
> data(delete and add info) to the work!!!!!.
>
> Is this possible?

Thanks. I believe since we're using O2K(Excel2k) I'm limited on how to set
up security.
I can protect sheet/workbook but then I can't use Sort or AutoFilter.
From what I've read, later versions of Excel will give you more options.

Will keep playing with tis.

Again thanks,


"Max" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Try pre-applying the autofilter, then protect* the sheet with a password
> Check: Use Autofilter in the Protect Sheet dialog**
> *via Tools > Protection > Protect Sheet
> **there's also a "Sort" option in the dialog to check, but I couldn't get
> it to work
> --
> Max
> Singapore
>
> "L.S." <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> ... BUT another thing we want to do is
>> protect the worksheet/workbook from someone changing the data.
>> We want people to be able to use the Sort and/or AutoFilter function to
>> get the info they need BUT we don't want them to be able to change the
>> data(delete and add info) to the work!!!!!.
>>
>> Is this possible?
>
>

Try this response by Peter T in excel.programming
Might be just what you need to get going here in xl2000 ...

" ... You need to set UserInterfaceOnly, but as the setting doesn't persist
between sessions need to set it in the open event.

Apply the filter on an unprotected sheet then run the macro. Also run the
macro from the open event.

'' ThisWorkbook module
Private Sub Workbook_Open()
ProtectAndFilter
End Sub

' normal module
Sub ProtectAndFilter()

With ThisWorkbook.Worksheets("Sheet1")
.Protect _
Password:="abc", _
DrawingObjects:=False, _
Contents:=True, _
Scenarios:=True, _
UserInterfaceOnly:=True

.EnableAutoFilter = True
End With

End Sub


--
Max
Singapore

"L.S." <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Thanks. I believe since we're using O2K(Excel2k) I'm limited on how to set
> up security.
> I can protect sheet/workbook but then I can't use Sort or AutoFilter.
> From what I've read, later versions of Excel will give you more options.
>
> Will keep playing with tis.
>
> Again thanks,