Splitting Data Across Worksheets
Posted by karen on February 17, 2001 5:35 AM
I have a worksheet containing 345600 lines of data, 8640 beginning in $a, 8640 beginning in $z and 17280 beginning in $w. I need t split this data up into three worksheets, with each type of data on a seperate worksheet. I have tried using:
Dim C as range
For each C in range (Range("A1"), range("A65536").end(xlup))
If C.Value = "$a" then
c.entirerow.copy
activesheet.paster sheets(c.value).range("a65536").end(xlup)
end if
next
for each of the row headings, but the computer keeps locking up on the third data type, and it takes an eternity to finish the first two.
Is there any easier way to do this?
Posted by Greg on February 17, 2001 10:17 AM
First, does this have to be automated? If not, you can very easily do what you want by selecting all the rows, then sorting them based on the first column. This will group all your $a's, $z's and $w's together. You can then cut and paste.
Let me know if this is not an adequate solution and I will suggest other ways to automate in VB.
Greg
Posted by karen on February 17, 2001 2:25 PM
it would be best automated as i have about 100 of these data tables to sort!
Posted by David Hawley on February 17, 2001 11:31 PM
Hi Karen
I would say yes! There is an easier way to do this. Loops are very slow. Use Excels AutoFilter in your VBA code and it will do it in a second.
The code below assumes you column to search is Column A and you already 3 sheets called Alla,Allz and Allw
Sub CopyOver()
With Sheets("Sheet1")
.AutoFilterMode = False
.Range("A1:F1").AutoFilter
.Range("A1:F1").AutoFilter Field:=1, Criteria1:="$a"
.UsedRange.SpecialCells(xlCellTypeVisible).EntireRow.Copy _
Destination:=Sheets("Alla").Range("A1")
.Range("A1:F1").AutoFilter Field:=1, Criteria1:="$z"
.UsedRange.SpecialCells(xlCellTypeVisible).EntireRow.Copy _
Destination:=Sheets("Allz").Range("A1")
.Range("A1:F1").AutoFilter Field:=1, Criteria1:="$w"
.UsedRange.SpecialCells(xlCellTypeVisible).EntireRow.Copy _
Destination:=Sheets("Allw").Range("A1")
.AutoFilterMode = False
End With
End Sub
Dave
OzGrid Business Applications
Posted by Karen on February 18, 2001 8:38 AM
Thank You - Works a treat!
