Copying certains rows to certain worksheets.

[Ron]

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.

 

[Joel]

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]

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]

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]

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]

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

 

[a a]

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

 

[Ron]

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

 

[Ron]

a a,

Looks interesting. I'll take a look at it. Thanks.