Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
copy row to different tab
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. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
copy row to different tab
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. . |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
copy row to different tab
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. . |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
copy row to different tab
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. . . |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
copy row to different tab
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" wrote in message ... 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. . . |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
copy row to different tab
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." wrote in message ... .... 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? |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
copy row to different tab
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" wrote in message ... 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." wrote in message ... ... 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? |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
copy row to different tab
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." wrote in message ... 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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy and paste versus copy and insert copied cells | New Users to Excel | |||
I copy a formula and the results copy from the original cell | Excel Discussion (Misc queries) | |||
Move/Copy or Copy/Insert worksheet? | Excel Discussion (Misc queries) | |||
Copy/Paste how to avoid the copy of formula cells w/o calc values | Excel Discussion (Misc queries) | |||
EXCEL FILE a copy/a copy/a copy ....filename | New Users to Excel |