Excel marco: Create new sheet from list of data

February 28, 2013 at 07:43:50
Specs: Windows 7
I found this VBA code for excel marco but it is creating new sheets from a list based on the date. However, I was curious if this can be changed to loop through the data list and create a new sheet for each unique customer.

Sub CreateMonthlySheets()
Dim lastRow, mMonth, tstDate1, tstDate2, shtName, nxtRow

On Error Resume Next

'Make a copy of the data sheet and sort by name
Sheets("Sheet1").Copy After:=Sheets(1)
Sheets(2).Name = "SortTemp"
With Sheets("SortTemp")
lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
Rows("2:" & lastRow).Sort Key1:=Range("A2"), Order1:=xlAscending

'Using SortTemp Sheet, create monthly sheets by
'testing Month and Year values in Column A

'Loop through dates
For Each mMonth In .Range("A2:A" & lastRow)
tstDate1 = Month(mMonth) & Year(mMonth)
tstDate2 = Month(mMonth.Offset(-1, 0)) & Year(mMonth.Offset(-1, 0))

'If Month and Year are different than cell above, create new sheet
If tstDate1 <> tstDate2 Then
ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)

'Name the sheet based on the Month and Year
ActiveSheet.Name = MonthName(Month(mMonth)) & " " & Year(mMonth)
End If
On Error GoTo 0

'Loop through dates, copying row to the correct sheet
For Each mMonth In .Range("A2:A" & lastRow)
'Create sheetname variable
shtName = MonthName(Month(mMonth)) & " " & Year(mMonth)
'Determine next empty row in sheet
nxtRow = Sheets(shtName).Cells(Rows.Count, 1).End(xlUp).Row + 1
'Copy Data
.Range(mMonth.Address).EntireRow.Copy Destination:=Sheets(shtName).Cells(nxtRow, 1)
End With
'Delete SortTemp sheet
Application.DisplayAlerts = False
Application.DisplayAlerts = True
End Sub

See More: Excel marco: Create new sheet from list of data

Report •

March 17, 2013 at 08:01:06
Where is the customer information located in the worksheet? Do you have any Named Ranges? How do you want the new sheet populated?

Report •
Related Solutions

Ask Question