VBA - HOW TO: SAVE WORKBOOK - RENAME SHEETS - CREATE BOOK
Yup, title says it all
Sub Create_New_Book()
Dim exl As Excel.Application
Dim wkb As Excel.Workbook
Dim sht As Excel.Worksheet
Set exl = New Excel.Application ' Bind Excel application
exl.Workbooks.Add ' this creates a new book of sheets... ish
exl.Visible = True ' makes it visible
k = 1
For Each sht In exl.Sheets 'Run through each sheet and rename
sht.Name = "THIS_is_IT " & k
k = k + 1
Next
Set wkb = exl.ActiveWorkbook 'Bind workbook to wkb
wkb.SaveAs Filename:=Environ("temp") & "\temp excel.xls" 'Save to temp location
End Sub