Grouping data on seperate Worksheets

[Cameron]

Hello,

I have an excel spreadsheet that draws monthly data from an Access Database.
This data is grouped based on months of the year and then sub grouped based
on dates during the month. what I would like to do for ease of use is have
the Spreadsheet understand that for each month it is to generate a seperate
worksheet for each month. Is this possible? And how would someone go about
coding the spreadsheet to look at the Months field and determine when to
build the new worksheet?

 

[Charlie]

Hi,

Here simple code taht can do it.

The code is base on a sheet Call "ACCESS DATA" with the month (i
order) in column A (y = 1) and the data start on row 2.


Sub GroupMonth()
Dim xStart As Integer
Dim xCurrent As Integer
Dim y As Integer
Dim MonthCurr As String
Dim SheetWithData As String

SheetWithData = "ACCESS DATA" 'Name of the sheet with the data
xCurrent = 2 'First row of data
y = 1 'Column where the Month to group are on the data sheet.

Sheets(SheetWithData).Select

MonthCurr = Trim(Cells(xCurrent, y))
xStart = xCurrent

Do While Len(Trim(Cells(xCurrent, y))) > 0
If MonthCurr <> Trim(Cells(xCurrent, y)) Then
Rows(xStart & ":" & xCurrent - 1).Select
Selection.Copy
Sheets.Add
ActiveSheet.Name = MonthCurr
Range("A1").Select
ActiveSheet.Paste

Sheets(SheetWithData).Select
MonthCurr = Trim(Cells(xCurrent, y))
xStart = xCurrent
End If
xCurrent = xCurrent + 1
Loop

Rows(xStart & ":" & xCurrent - 1).Select
Selection.Copy
Sheets.Add
ActiveSheet.Name = MonthCurr
Range("A1").Select
ActiveSheet.Paste

End Sub


Charlie
'Opener Consulting Home' (http://www.openerconsulting.com)