Solved Excel Macro to create new worksheet in Excel 2010

April 26, 2013 at 08:35:56
Specs: Windows 7
With due respect and credit to DerbyDad03 whose earlier post gave me the code below; is there any way to have the new worksheets created from a variable length field e.g. a persons name in a list or a field of fixed length other than a date as the script below uses?


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

On Error Resume Next
'Turn off ScreenUpdating
Application.ScreenUpdating = False
'Make a copy of the data sheet and sort by date
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)
'Copy Column Widths and Header Row
.Rows(1).Copy
ActiveSheet.Rows(1).PasteSpecial Paste:=8 'ColumnWidth
ActiveSheet.Rows(1).PasteSpecial 'Data and Formats
End If
Next
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)
Next
End With
'Delete SortTemp sheet
Application.DisplayAlerts = False
Sheets("SortTemp").Delete
Application.DisplayAlerts = True
'Turn on ScreenUpdating
Application.ScreenUpdating = True
End Sub


See More: Excel Macro to create new worksheet in Excel 2010

Report •

#1
April 29, 2013 at 10:59:17
✔ Best Answer
I created a quick macro to help you out. This macro creates a new sheet and names it using the contents of cell "A1".

Sub CreateNewSheet()
    Dim NewSheet
    Dim SheetName As String
    
    SheetName = Worksheets("Sheet1").Cells(1, "A").Value
    Set NewSheet = Sheets.Add(After:=Sheets(Worksheets.Count), Count:=1, Type:=xlWorksheet)
    NewSheet.Name = SheetName
    
End Sub

Law if Logical Argument: Anything is possible if you don't know what you're talking about.


Report •

#2
April 29, 2013 at 11:49:55
If you want the Macro to prompt you for a name every time, you would use something similar to this: (Note: You can also use Application.InputBox if you need more functionality. See this site for proper syntax: http://msdn.microsoft.com/en-us/lib...

Sub CreateNewSheet()
    Dim NewSheet
    Dim SheetName As String
    
    SheetName = InputBox("Please Enter Name of New Sheet", "You Must Enter Something!")
        If Trim(SheetName) = "" Then
                MsgBox "You did not enter something. New Sheet Creation Cancled."
                Exit Sub
            Else
        End If
        
    Set NewSheet = Sheets.Add(After:=Sheets(Worksheets.Count), Count:=1, Type:=xlWorksheet)
    NewSheet.Name = SheetName
    
End Sub

Law if Logical Argument: Anything is possible if you don't know what you're talking about.


Report •

#3
April 29, 2013 at 11:50:41
This is the proper link I referred to in the last post.

http://msdn.microsoft.com/en-us/lib...

Law if Logical Argument: Anything is possible if you don't know what you're talking about.


Report •
Related Solutions


Ask Question