Automatically create a new sheet adding one digit to the total

[BrianClough]

Hi,

Due to the way Excel works out dates, 1900 onwards. My worksheet names are numbered for the serial number of the date, on the sheet itself I have an =MID to call the worksheet name, then I convert that in to a readable date in another cell.

This is probably a odd way of doing it, but I can't seem to get DATEVALUE to work, well, not the way I intend, and Excel won't allow you to use / in sheet names, so I found this way works best. (Unless anyone can advise me differently?)

Now, my other Excel users at work, are basic end users, they fill in boxes and we don't expect anything more than that.

The sheet needs to be duplicated at the start of the day.

Is there any way, I could implement a macro to automatically duplicate the last sheet, and add an extra digit to the total for the new sheet at all?

Regards,

Brian

 

[Logit]

The following will copy the active sheet and paste the contents to a new sheet that is added after all existing sheets.

I do not understand "add an extra digit to the total for the new sheet" .... ???

Option Explicit


Sub cpyTemplate()
Application.ScreenUpdating = False


    Columns("A:P").Select '<-- edit range here
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    Columns("A:P").Select
    ActiveSheet.Paste
    Range("B4").Select
    Application.Sheets((Sheets.Count) - 1).Select
    Range("B4").Select
    Application.CutCopyMode = False


Application.ScreenUpdating = True
End Sub

 

[BrianClough]

Currently my sheets are named for the serial number of the date. 43440, being today. Tomorrow's will be 43441. This is what I meant by adding one digit to the total.

I'm using the serial format instead of an actual date, as when I am pulling the sheet name into a cell via formula:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

This shows me the name, and then I convert it into a readable date in another cell, this then allows me to display different dates in other cells to my needs.

I don't seem to be able to put a ddmmyyyy into the sheet name, and using the =MID or =DATEVALUE, to be able to achieve the same result. I suspect I am doing something wrong.

 

[Logit]

.
The following example presumes your serial date is located in A1. Rather than use a formula to create the next number, this macro adds one to the existing.

Sub cpyTemplate()


Application.ScreenUpdating = False


    Columns("A:P").Select '<-- edit range here
    Selection.Copy
    Range("A1").Select
    Sheets.Add After:=Sheets(Sheets.Count)
    Range("A1").Select
    ActiveSheet.Paste
    ActiveSheet.Range("A1").Value = ActiveSheet.Range("A1").Value + 1
    Range("A1").Select
    Application.CutCopyMode = False




Application.ScreenUpdating = True


End Sub

 

[Zenwood]

Another way to go.

Dim Sheetname_ As String


Sheetname_ = ActiveSheet.Name
Sheetname_ = Sheetname_ + 1
    
Cells.Select
Selection.Copy
Sheets.Add After:=ActiveSheet
Cells.Select
ActiveSheet.Paste
ActiveSheet.Name = Sheetname_


End Sub

 

[BrianClough]

Is there any way, this can rename the sheet, rather than changing the cells?

Sorry to be a pain

Regards,

Brian

 

[BrianClough]

Thank you very much, both of you. That works perfectly.

Kind Regards,

Brian

 

[BrianClough]

At risk of sounding cheeky, is there any way this could catch an error, and stop the process duplicating, if the user is already on a sheet, where that page has already been duplicated? To stop the function spitting out errors?

VBA is not a strength of mine.

Regards,

Brian