copy row to different tab

  • Thread starter Thread starter L.S.
  • Start date Start date
L

L.S.

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".
 
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 said:
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. said:
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.


.
Click to expand...
Click to expand...
 
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. said:
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 said:
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. said:
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.


.
Click to expand...
Click to expand...


.
Click to expand...
 
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 said:
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. said:
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 said:
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".
:

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.


.
Click to expand...


.
Click to expand...
Click to expand...
 
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
 
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,
 
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
 
Back
Top