External Data on diff worksheets
Posted by Chris Brick on February 12, 2002 3:27 AM
Is it possible to loop through a list of data obtained from an Access query - and placing rows on different worksheets dependent upon one field. ie when the company id changes to move to next worksheet & continue refreshing data & so on & so on. The final result would be companies results displayed on different worksheets, but originating from one large query.
Posted by Ed on February 12, 2002 8:29 AM
This code does what you ask. I will e-mail you the spreadsheet so you can see how it works and modify to fit your needs.
Dim RV, CV As String
Dim C, SR As Integer
Cells.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes
Range("A2").Select
SR = 2
Application.ScreenUpdating = False
For C = 2 To Range("A65536").End(xlUp).Row
RV = Range("A" & C)
CV = Range("A" & C + 1)
If RV <> CV Then _
Rows(SR & ":" & C).Select
Selection.Copy
Sheets.Add.Name = RV
ActiveSheet.Paste
Sheets("Accessinfo").Select
SR = C + 1
End If
Application.ScreenUpdating = True
Next C
