| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying certains rows to certain worksheets.
I have a sheet with 14 columns of data. Up to 3000 rows, counting a header row.
Column I has a key that I look at to determine which other worksheet to copy the data to. For instance, rows with AA, AB, and XY (in column I)I need to copy to a Sheet named TS. Rows with AC, DF, SS I need to copy to a Sheet named CS. And Rows with XX I need to copy to a Sheet named XX. Of course, it has to copy to the next blank row on each sheet, so all of the AA's, AB's and XY's are on sheet TS. Etc. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copying certains rows to certain worksheets.
Try thiis
With ActiveSheet RowCount = 2 Do While .Range("I" & RowCount) < "" Select Case .Range("I" & RowCount) Case "AA", "AB", "XY" DestSht = Sheets("TS") Case "AC", "DF", "SS" DestSht = Sheets("CS") Case "XX" DestSht = Sheets("XX") End Select LastRow = DestSht.Range("A" & Rows.Count).End(xlUp).Row Newrow = LastRow + 1 .Rows(RowCount).Copy _ Destination:=DestSht.Rows(Newrow) RowCount = RowCount + 1 Loop End With "Ron" wrote: I have a sheet with 14 columns of data. Up to 3000 rows, counting a header row. Column I has a key that I look at to determine which other worksheet to copy the data to. For instance, rows with AA, AB, and XY (in column I)I need to copy to a Sheet named TS. Rows with AC, DF, SS I need to copy to a Sheet named CS. And Rows with XX I need to copy to a Sheet named XX. Of course, it has to copy to the next blank row on each sheet, so all of the AA's, AB's and XY's are on sheet TS. Etc. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copying certains rows to certain worksheets.
Joel,
It looks like it would work, however, I have 2 problems. 1. While I try to compile, it points to "LastRow =" and says Compile Error, Argument not optional. I changed the name to OldRow and it worked. Not sure why. 2. After fixing that, I get to "DestSht = Sheets("TS")" and it says Runtime error 438, Object doesn't support this property or method. I changed DestSht to Dsht and it didn't help. I'm stumped. Ron "Joel" wrote: Try thiis With ActiveSheet RowCount = 2 Do While .Range("I" & RowCount) < "" Select Case .Range("I" & RowCount) Case "AA", "AB", "XY" DestSht = Sheets("TS") Case "AC", "DF", "SS" DestSht = Sheets("CS") Case "XX" DestSht = Sheets("XX") End Select LastRow = DestSht.Range("A" & Rows.Count).End(xlUp).Row Newrow = LastRow + 1 .Rows(RowCount).Copy _ Destination:=DestSht.Rows(Newrow) RowCount = RowCount + 1 Loop End With "Ron" wrote: I have a sheet with 14 columns of data. Up to 3000 rows, counting a header row. Column I has a key that I look at to determine which other worksheet to copy the data to. For instance, rows with AA, AB, and XY (in column I)I need to copy to a Sheet named TS. Rows with AC, DF, SS I need to copy to a Sheet named CS. And Rows with XX I need to copy to a Sheet named XX. Of course, it has to copy to the next blank row on each sheet, so all of the AA's, AB's and XY's are on sheet TS. Etc. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copying certains rows to certain worksheets.
I didn't know the amount of experience you had with macros. I made one
change below seting DestSht to a sheet name. change to the correct sheet name you are using. row.Count should work. I suspect you don't have all the proper settint in excel check these options 1) On worksheet menu Tools - OPtions - addin Make sure these two itmes are checked a) Analysis ToolPak b) Analysis Toolpak - VBA 2) On VBA menu tools - References make sure these 4 items are checked a) Visual Basic for Applications b) Microsoft Excel 10.0 Object library (or latest on your PC) c) OLE Automation d) Microsoft Office 10.0 Object Library These are the standard items that normally are selected with excel. Sometimes they get unchecked. Sub test() Set DestSht = Sheets("Sheet2") With ActiveSheet RowCount = 2 Do While .Range("I" & RowCount) < "" Select Case .Range("I" & RowCount) Case "AA", "AB", "XY" DestSht = Sheets("TS") Case "AC", "DF", "SS" DestSht = Sheets("CS") Case "XX" DestSht = Sheets("XX") End Select LastRow = DestSht.Range("A" & Rows.Count).End(xlUp).Row Newrow = LastRow + 1 .Rows(RowCount).Copy _ Destination:=DestSht.Rows(Newrow) RowCount = RowCount + 1 Loop End With End Sub "Ron" wrote: Joel, It looks like it would work, however, I have 2 problems. 1. While I try to compile, it points to "LastRow =" and says Compile Error, Argument not optional. I changed the name to OldRow and it worked. Not sure why. 2. After fixing that, I get to "DestSht = Sheets("TS")" and it says Runtime error 438, Object doesn't support this property or method. I changed DestSht to Dsht and it didn't help. I'm stumped. Ron "Joel" wrote: Try thiis With ActiveSheet RowCount = 2 Do While .Range("I" & RowCount) < "" Select Case .Range("I" & RowCount) Case "AA", "AB", "XY" DestSht = Sheets("TS") Case "AC", "DF", "SS" DestSht = Sheets("CS") Case "XX" DestSht = Sheets("XX") End Select LastRow = DestSht.Range("A" & Rows.Count).End(xlUp).Row Newrow = LastRow + 1 .Rows(RowCount).Copy _ Destination:=DestSht.Rows(Newrow) RowCount = RowCount + 1 Loop End With "Ron" wrote: I have a sheet with 14 columns of data. Up to 3000 rows, counting a header row. Column I has a key that I look at to determine which other worksheet to copy the data to. For instance, rows with AA, AB, and XY (in column I)I need to copy to a Sheet named TS. Rows with AC, DF, SS I need to copy to a Sheet named CS. And Rows with XX I need to copy to a Sheet named XX. Of course, it has to copy to the next blank row on each sheet, so all of the AA's, AB's and XY's are on sheet TS. Etc. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copying certains rows to certain worksheets.
Joel,
All my options were set right...using 2007, so object library is 12. This is what I did to get it working, based on what you said. Set DestSht1 = Sheets("TS") Set DestSht2 = Sheets("CS") Set DestSht3 = Sheets("Abuse") Set DestSht4 = Sheets("SADMIN") Then, in each case I did: Set DestSht = DestSht1 (or 2 or 3 or 4) Not sure if I needed all that, but it worked. My experience is chopping all these responses apart and making them work somehow. I appreciate what you sent...all you guys are great teachers. Ron "Joel" wrote: I didn't know the amount of experience you had with macros. I made one change below seting DestSht to a sheet name. change to the correct sheet name you are using. row.Count should work. I suspect you don't have all the proper settint in excel check these options 1) On worksheet menu Tools - OPtions - addin Make sure these two itmes are checked a) Analysis ToolPak b) Analysis Toolpak - VBA 2) On VBA menu tools - References make sure these 4 items are checked a) Visual Basic for Applications b) Microsoft Excel 10.0 Object library (or latest on your PC) c) OLE Automation d) Microsoft Office 10.0 Object Library These are the standard items that normally are selected with excel. Sometimes they get unchecked. Sub test() Set DestSht = Sheets("Sheet2") With ActiveSheet RowCount = 2 Do While .Range("I" & RowCount) < "" Select Case .Range("I" & RowCount) Case "AA", "AB", "XY" DestSht = Sheets("TS") Case "AC", "DF", "SS" DestSht = Sheets("CS") Case "XX" DestSht = Sheets("XX") End Select LastRow = DestSht.Range("A" & Rows.Count).End(xlUp).Row Newrow = LastRow + 1 .Rows(RowCount).Copy _ Destination:=DestSht.Rows(Newrow) RowCount = RowCount + 1 Loop End With End Sub "Ron" wrote: Joel, It looks like it would work, however, I have 2 problems. 1. While I try to compile, it points to "LastRow =" and says Compile Error, Argument not optional. I changed the name to OldRow and it worked. Not sure why. 2. After fixing that, I get to "DestSht = Sheets("TS")" and it says Runtime error 438, Object doesn't support this property or method. I changed DestSht to Dsht and it didn't help. I'm stumped. Ron "Joel" wrote: Try thiis With ActiveSheet RowCount = 2 Do While .Range("I" & RowCount) < "" Select Case .Range("I" & RowCount) Case "AA", "AB", "XY" DestSht = Sheets("TS") Case "AC", "DF", "SS" DestSht = Sheets("CS") Case "XX" DestSht = Sheets("XX") End Select LastRow = DestSht.Range("A" & Rows.Count).End(xlUp).Row Newrow = LastRow + 1 .Rows(RowCount).Copy _ Destination:=DestSht.Rows(Newrow) RowCount = RowCount + 1 Loop End With "Ron" wrote: I have a sheet with 14 columns of data. Up to 3000 rows, counting a header row. Column I has a key that I look at to determine which other worksheet to copy the data to. For instance, rows with AA, AB, and XY (in column I)I need to copy to a Sheet named TS. Rows with AC, DF, SS I need to copy to a Sheet named CS. And Rows with XX I need to copy to a Sheet named XX. Of course, it has to copy to the next blank row on each sheet, so all of the AA's, AB's and XY's are on sheet TS. Etc. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copying certains rows to certain worksheets.
Here are two slightly different versions or the macro that might work for you
Sub test() Set SourceSht = activesheet SHtNames = Array("TS","CS","Abuse","SADMIN") for each sht in ShtNames Set DestSht = Sheets(Sht) With SourceSht RowCount = 2 Do While .Range("I" & RowCount) < "" Select Case .Range("I" & RowCount) Case "AA", "AB", "XY" DestSht = Sheets("TS") Case "AC", "DF", "SS" DestSht = Sheets("CS") Case "XX" DestSht = Sheets("XX") End Select LastRow = DestSht.Range("A" & Rows.Count).End(xlUp).Row Newrow = LastRow + 1 .Rows(RowCount).Copy _ Destination:=DestSht.Rows(Newrow) RowCount = RowCount + 1 Loop End With next Sht End Sub Sub test() Set SourceSht = activesheet for each sht in Sheets if Sht.name < SourceSht.name then Set DestSht = Sheets(Sht) With SourceSht RowCount = 2 Do While .Range("I" & RowCount) < "" Select Case .Range("I" & RowCount) Case "AA", "AB", "XY" DestSht = Sheets("TS") Case "AC", "DF", "SS" DestSht = Sheets("CS") Case "XX" DestSht = Sheets("XX") End Select LastRow = DestSht.Range("A" & Rows.Count).End(xlUp).Row Newrow = LastRow + 1 .Rows(RowCount).Copy _ Destination:=DestSht.Rows(Newrow) RowCount = RowCount + 1 Loop End With end if next sht End Sub "Ron" wrote: Joel, All my options were set right...using 2007, so object library is 12. This is what I did to get it working, based on what you said. Set DestSht1 = Sheets("TS") Set DestSht2 = Sheets("CS") Set DestSht3 = Sheets("Abuse") Set DestSht4 = Sheets("SADMIN") Then, in each case I did: Set DestSht = DestSht1 (or 2 or 3 or 4) Not sure if I needed all that, but it worked. My experience is chopping all these responses apart and making them work somehow. I appreciate what you sent...all you guys are great teachers. Ron "Joel" wrote: I didn't know the amount of experience you had with macros. I made one change below seting DestSht to a sheet name. change to the correct sheet name you are using. row.Count should work. I suspect you don't have all the proper settint in excel check these options 1) On worksheet menu Tools - OPtions - addin Make sure these two itmes are checked a) Analysis ToolPak b) Analysis Toolpak - VBA 2) On VBA menu tools - References make sure these 4 items are checked a) Visual Basic for Applications b) Microsoft Excel 10.0 Object library (or latest on your PC) c) OLE Automation d) Microsoft Office 10.0 Object Library These are the standard items that normally are selected with excel. Sometimes they get unchecked. Sub test() Set DestSht = Sheets("Sheet2") With ActiveSheet RowCount = 2 Do While .Range("I" & RowCount) < "" Select Case .Range("I" & RowCount) Case "AA", "AB", "XY" DestSht = Sheets("TS") Case "AC", "DF", "SS" DestSht = Sheets("CS") Case "XX" DestSht = Sheets("XX") End Select LastRow = DestSht.Range("A" & Rows.Count).End(xlUp).Row Newrow = LastRow + 1 .Rows(RowCount).Copy _ Destination:=DestSht.Rows(Newrow) RowCount = RowCount + 1 Loop End With End Sub "Ron" wrote: Joel, It looks like it would work, however, I have 2 problems. 1. While I try to compile, it points to "LastRow =" and says Compile Error, Argument not optional. I changed the name to OldRow and it worked. Not sure why. 2. After fixing that, I get to "DestSht = Sheets("TS")" and it says Runtime error 438, Object doesn't support this property or method. I changed DestSht to Dsht and it didn't help. I'm stumped. Ron "Joel" wrote: Try thiis With ActiveSheet RowCount = 2 Do While .Range("I" & RowCount) < "" Select Case .Range("I" & RowCount) Case "AA", "AB", "XY" DestSht = Sheets("TS") Case "AC", "DF", "SS" DestSht = Sheets("CS") Case "XX" DestSht = Sheets("XX") End Select LastRow = DestSht.Range("A" & Rows.Count).End(xlUp).Row Newrow = LastRow + 1 .Rows(RowCount).Copy _ Destination:=DestSht.Rows(Newrow) RowCount = RowCount + 1 Loop End With "Ron" wrote: I have a sheet with 14 columns of data. Up to 3000 rows, counting a header row. Column I has a key that I look at to determine which other worksheet to copy the data to. For instance, rows with AA, AB, and XY (in column I)I need to copy to a Sheet named TS. Rows with AC, DF, SS I need to copy to a Sheet named CS. And Rows with XX I need to copy to a Sheet named XX. Of course, it has to copy to the next blank row on each sheet, so all of the AA's, AB's and XY's are on sheet TS. Etc. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copying certains rows to certain worksheets.
On Aug 20, 11:07*pm, Ron wrote:
I have a sheet with 14 columns of data. Up to 3000 rows, counting a header row. Column I has a key that I look at to determine which other worksheet to copy the data to. For instance, rows with AA, AB, and XY (in column I)I need to copy to a Sheet named TS. Rows with AC, DF, SS I need to copy to a Sheet named CS. And Rows with XX I need to copy to a Sheet named XX. Of course, it has to copy to the next blank row on each sheet, so all of the AA's, AB's and XY's are on sheet TS. Etc. Hi, try using GemBox.Spreadsheet component. It is very easy to use and works very fast. http://www.gemboxsoftware.com/GBSpreadsheet.htm |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copying certains rows to certain worksheets.
Thanks - I'll play with them and see what I can do with them. I have it
working now. I'm doing a similar thing on my destination sheets, finding a value in a column and want to cut it and put it in a third sheet. Case doesn't seem to be the best fit for that as it finds what I want, but on all the other rows, it wants a valid destination sheet also. Any quick and dirty solutions to do that? I'm sure I can use a clunky if loop, but that is not efficient. Again, I can't thank you enough. Ron "Joel" wrote: Here are two slightly different versions or the macro that might work for you Sub test() Set SourceSht = activesheet SHtNames = Array("TS","CS","Abuse","SADMIN") for each sht in ShtNames Set DestSht = Sheets(Sht) With SourceSht RowCount = 2 Do While .Range("I" & RowCount) < "" Select Case .Range("I" & RowCount) Case "AA", "AB", "XY" DestSht = Sheets("TS") Case "AC", "DF", "SS" DestSht = Sheets("CS") Case "XX" DestSht = Sheets("XX") End Select LastRow = DestSht.Range("A" & Rows.Count).End(xlUp).Row Newrow = LastRow + 1 .Rows(RowCount).Copy _ Destination:=DestSht.Rows(Newrow) RowCount = RowCount + 1 Loop End With next Sht End Sub Sub test() Set SourceSht = activesheet for each sht in Sheets if Sht.name < SourceSht.name then Set DestSht = Sheets(Sht) With SourceSht RowCount = 2 Do While .Range("I" & RowCount) < "" Select Case .Range("I" & RowCount) Case "AA", "AB", "XY" DestSht = Sheets("TS") Case "AC", "DF", "SS" DestSht = Sheets("CS") Case "XX" DestSht = Sheets("XX") End Select LastRow = DestSht.Range("A" & Rows.Count).End(xlUp).Row Newrow = LastRow + 1 .Rows(RowCount).Copy _ Destination:=DestSht.Rows(Newrow) RowCount = RowCount + 1 Loop End With end if next sht End Sub "Ron" wrote: Joel, All my options were set right...using 2007, so object library is 12. This is what I did to get it working, based on what you said. Set DestSht1 = Sheets("TS") Set DestSht2 = Sheets("CS") Set DestSht3 = Sheets("Abuse") Set DestSht4 = Sheets("SADMIN") Then, in each case I did: Set DestSht = DestSht1 (or 2 or 3 or 4) Not sure if I needed all that, but it worked. My experience is chopping all these responses apart and making them work somehow. I appreciate what you sent...all you guys are great teachers. Ron "Joel" wrote: I didn't know the amount of experience you had with macros. I made one change below seting DestSht to a sheet name. change to the correct sheet name you are using. row.Count should work. I suspect you don't have all the proper settint in excel check these options 1) On worksheet menu Tools - OPtions - addin Make sure these two itmes are checked a) Analysis ToolPak b) Analysis Toolpak - VBA 2) On VBA menu tools - References make sure these 4 items are checked a) Visual Basic for Applications b) Microsoft Excel 10.0 Object library (or latest on your PC) c) OLE Automation d) Microsoft Office 10.0 Object Library These are the standard items that normally are selected with excel. Sometimes they get unchecked. Sub test() Set DestSht = Sheets("Sheet2") With ActiveSheet RowCount = 2 Do While .Range("I" & RowCount) < "" Select Case .Range("I" & RowCount) Case "AA", "AB", "XY" DestSht = Sheets("TS") Case "AC", "DF", "SS" DestSht = Sheets("CS") Case "XX" DestSht = Sheets("XX") End Select LastRow = DestSht.Range("A" & Rows.Count).End(xlUp).Row Newrow = LastRow + 1 .Rows(RowCount).Copy _ Destination:=DestSht.Rows(Newrow) RowCount = RowCount + 1 Loop End With End Sub "Ron" wrote: Joel, It looks like it would work, however, I have 2 problems. 1. While I try to compile, it points to "LastRow =" and says Compile Error, Argument not optional. I changed the name to OldRow and it worked. Not sure why. 2. After fixing that, I get to "DestSht = Sheets("TS")" and it says Runtime error 438, Object doesn't support this property or method. I changed DestSht to Dsht and it didn't help. I'm stumped. Ron "Joel" wrote: Try thiis With ActiveSheet RowCount = 2 Do While .Range("I" & RowCount) < "" Select Case .Range("I" & RowCount) Case "AA", "AB", "XY" DestSht = Sheets("TS") Case "AC", "DF", "SS" DestSht = Sheets("CS") Case "XX" DestSht = Sheets("XX") End Select LastRow = DestSht.Range("A" & Rows.Count).End(xlUp).Row Newrow = LastRow + 1 .Rows(RowCount).Copy _ Destination:=DestSht.Rows(Newrow) RowCount = RowCount + 1 Loop End With "Ron" wrote: I have a sheet with 14 columns of data. Up to 3000 rows, counting a header row. Column I has a key that I look at to determine which other worksheet to copy the data to. For instance, rows with AA, AB, and XY (in column I)I need to copy to a Sheet named TS. Rows with AC, DF, SS I need to copy to a Sheet named CS. And Rows with XX I need to copy to a Sheet named XX. Of course, it has to copy to the next blank row on each sheet, so all of the AA's, AB's and XY's are on sheet TS. Etc. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copying certains rows to certain worksheets.
a a,
Looks interesting. I'll take a look at it. Thanks. "a a" wrote: On Aug 20, 11:07 pm, Ron wrote: I have a sheet with 14 columns of data. Up to 3000 rows, counting a header row. Column I has a key that I look at to determine which other worksheet to copy the data to. For instance, rows with AA, AB, and XY (in column I)I need to copy to a Sheet named TS. Rows with AC, DF, SS I need to copy to a Sheet named CS. And Rows with XX I need to copy to a Sheet named XX. Of course, it has to copy to the next blank row on each sheet, so all of the AA's, AB's and XY's are on sheet TS. Etc. Hi, try using GemBox.Spreadsheet component. It is very easy to use and works very fast. http://www.gemboxsoftware.com/GBSpreadsheet.htm |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copying header rows and inserting them into all worksheets | Excel Programming | |||
| Copying multiple rows to other worksheets (but amount of rows varies) - How? | Excel Discussion (Misc queries) | |||
| Copying rows to different worksheets based on a cells value | Excel Programming | |||
| Copying random rows to other worksheets | Excel Programming | |||
| copying cells into different rows in different worksheets | Excel Programming | |||
Linear Mode
