PDA

View Full Version : Solved: Split large data in various sheets



anandbohra
10-10-2007, 12:06 AM
:hi: All

I am having Data of 20 Companies for 450 daya (total rows = 9000)
now through VBA i created individual sheets for all 20 companies
now i want to split that 900 records to all the sheets respectively as per company name can any one provide me VBA code for that

data is arrange as follows
Co Name - Date - Open - high - low - close
abb - 1/1/07 - 10-11-9-10
acc - 1/1/07 - 15-17-15-16
.......

& so on
now i want all the data which contains ABB in column A goes to Sheet named as ABB with full records & so on

pl help me.

anandbohra
10-10-2007, 12:45 AM
:hi: found the solution
the name of file is distribute data
the code is


Option Explicit

Sub DistributeData()

Dim i As Long
Dim LastRow As Long
Dim ws As Worksheet
Dim ErrorLog As String

With Sheets("Main")
LastRow = .Range("A65536").End(xlUp).Row
For i = 2 To LastRow
On Error Resume Next
Set ws = Sheets(.Range("A" & i).Text)
On Error GoTo 0
If ws Is Nothing Then
ErrorLog = ErrorLog & vbNewLine & _
"Row:" & i & " Sheet Name: " & .Range("A" & i).Text
Else
.Range("A" & i).EntireRow.Copy _
Destination:=ws.Range("A65536").End(xlUp).Offset(1, 0)
End If
Set ws = Nothing
Next i
End With

If ErrorLog <> "" Then
ErrorLog = "The following worksheets could not be found " & _
"and the data was not transfered over." & vbNewLine & vbNewLine & ErrorLog
MsgBox ErrorLog
End If

Set ws = Nothing

End Sub