MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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

Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes
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
Sheets.Add.Name = RV
SR = C + 1
End If

Application.ScreenUpdating = True

Next C