VBA coding help [Adding new worksheets before the last sheet in Excel | Renaming WS]

[Jinru]

Hello

I wish to design a VBA that allows me to alter the number of sheets in a template excel file
The template excel file contains a set of blank sheets, and a final summary sheet at the end, hence I wish the VBA to adjust the number before the summary sheet

If possible, I would also like the VBA to rename all the blank to the specific dates ie ddmmyyyy

Ideally, the VBA is designed within excel, and I could enter the number of blank sheet I want, and the range of the dates, and the VBA detects it and adjust my template sheet automatically

Many thanks

Thsi is what I have so far, I'm a programming noob

Sub newsheet()
Dim newsheet As Worksheet

Set wks = Worksheets.Add(after:=Worksheets(Worksheets.Count - 1))
wks.Name = "Sheet" & Worksheets.Count + 1
With wks
.Cells(1, 1) = "TTL Corp."
.Cells.Font.Bold = True
.Cells(1, 6) = Date
End With
End Sub

[TysonLPrice]

A very handy way of getting an idea of the code you need is to do what you want while recording a macro. Then you can look at the code that was generated. About half way down this link in the "How do I write code to … ? [a.k.a. recording macro's]" section is an explanation.

http://www.vbforums.com/showthread.p...6-(or-VB5-VBA)

[Jinru]

A very handy way of getting an idea of the code you need is to do what you want while recording a macro. Then you can look at the code that was generated. About half way down this link in the "How do I write code to … ? [a.k.a. recording macro's]" section is an explanation.

http://www.vbforums.com/showthread.p...6-(or-VB5-VBA)

Thank you, will give that a go

[westconn1]

try like

cnt = 'number of sheets to add
strtdate = ' date for first sheet name
for i = 1 to cnt
  Set wks = Worksheets.Add(after:=Worksheets(Worksheets.Count - 1))
  wks.name = format(strtdate, "ddmmyyyy")
  strtdat = strtdate + 1
next

where strtdate is a date variable, cnt can be from a textbox, inputbox or any other user input
sheets are, in this case, incremented daily, if you prefer weekly use + 7 instead, etc

[Hack]

VBA question moved to Office Development