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

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