Creating a new sheet with specific formatting using excel VBA

Hi,

I have a macro button which upon clicking creates a new sheet titled with the current date. I would like the sheet to be created with a specific formatting (the same formatting for every new sheet created). I suppose this may be linked to creating a template but I'm not sure how best to do this. Would it also be possible to maybe along with the code for creating a new sheet to add in specific formatting commands such as making column C a certain width etc or cell D3 the colour red (all for example). I am trying to design the workbook to be able to be accessible via the cloud so would going down the template route cause issues (in terms of searching for the template directory?).

Many Thanks,

I have tried to do as you suggested but I am now having issues with my current code:

Private Sub cmdShowForm_Click()

Dim a As String
Dim exists As Boolean
Dim sh As Worksheet

For i = 1 To Worksheets.Count
If Worksheets(i).Name = Date$ Then
exists = True
Else
exists = False
End If
Next i

If exists = False Then
Sheets("TEMPLATE").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = Date$ (<- ISSUE IS HIGHLIGHTED HERE BY THE VBA EDITOR)
Set sh = ActiveSheet
ElseIf exists = True Then
ThisWorkbook.Sheets(Date$).Activate
End If

End Sub

The sub routine does effectively create a new sheet with the current date however upon revisiting the sub (by clicking the button) a new sheet is created titled ("TEMPLATE(2)") and an error is highlighted in the code. How can I get it to work? I want it to check through the sheets to detect if a sheet exists that is titled by the current date. If there isn't one I want one to be created that is titled by the current date and follows the format from the "TEMPLATE" sheet. If one does already exist (i.e the button is clicked more than once in a day) I want it to go straight to that sheet and not create anything more.

Any help would be greatly appreciated!

How about

Please Login or Register  to view this content.

change format in red to your preference.