PDA

View Full Version : Arrange data sheet wise



machunt
03-12-2012, 04:32 AM
Hi,
I again need some help, and was hoping somebody could please guide me around with a vba script which could arrange the data.
I have a huge file of data report around 80MB.From which I have chucked out to only three columns of data. What I was hoping to summarize the data sheet wise with the other two column.
I have generate a dummy file for final reference.
I have create a before and after sheet for making it easier to understand what I exactly I want. Sorry I could not upload two files so I zipped the files in a single file. Hope I am not violating any rule.
Best Regards,
Mac

Bob Phillips
03-12-2012, 05:03 AM
Sub ProcessData()
Dim sh As Worksheet
Dim lastrow As Long
Dim i As Long

With ActiveWorkbook.ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastrow

Set sh = Nothing
On Error Resume Next
Set sh = Parent.Worksheets(.Cells(i, "B").Value2)
On Error GoTo 0

If sh Is Nothing Then

Set sh = Parent.Worksheets.Add(after:=Parent.Worksheets(Parent.Worksheets.Count))
sh.Name = .Cells(i, "B").Value2
.Range("A1").Copy sh.Range("A1")
.Range("C1").Copy sh.Range("B1")
.Cells(i, "A").Copy sh.Range("A2")
.Cells(i, "C").Copy sh.Range("B2")
Else

.Cells(i, "A").Copy sh.Range("A1").End(xlDown).Offset(1, 0)
.Cells(i, "C").Copy sh.Range("B1").End(xlDown).Offset(1, 0)
End If
Next i

Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With
End Sub

machunt
03-12-2012, 05:33 AM
Hi xld,
Thanks for your reply. It's perfect,But Just a request.
1.Can the data be arranged in ascending order date wise in the generated sheets.
2.If I run the run the code again,can the sheets be deleted and recreated or rather only update the new records so that it is not overlapping.
I really appreciate your help on this.Till now it's working great. If I encounter any problem can I get back to you in future?
Thanks once again xld.
Best regards,
Mac