Task that Copy data to new workbook multiple times a day; need help with naming new sheet

[mikeHunt]

So I am working on a project to automate the pull of data from my companies website. I have figured out how to do the majority of the project; I am having trouble when I have to name the new sheet to write the data to. my code is as follows..

With y.Sheets("Sheet1").Range("A1")
.PasteSpecial x1PasteFormats
.PasteSpecial x1PasteValues
end With

What can I do so I do not have to go make a sheet named "Sheet1" every time the program runs. It would be dope if the sheet name could be the date and time. Any Help Would be Great

 

[Fluff]

Hi & welcome to the board.
How about

y.Sheets.Add.name = format(Now, "dd-mm-yyyy hh-mm-ss")
With ActiveSheet.Range("A1")
   .PasteSpecial xlPasteFormats
   .PasteSpecial xlPasteValues
End With

 

[davesexcel]

You can use a macro to add the sheet and name it.

Sub AddSht()
    Dim d As String
    d = Format(Now, "MM-DD-YY_hh-mm-ss")
    Sheets.Add.Name = d
End Sub

 

[mikeHunt]

Hi & welcome to the board.
How about

y.Sheets.Add.name = format(Now, "dd-mm-yyyy hh-mm-ss")
With ActiveSheet.Range("A1")
   .PasteSpecial xlPasteFormats
   .PasteSpecial xlPasteValues
End With

Thanks Excel Genius!

 

[Fluff]

Glad we could help & thanks for the feedback