PDA

View Full Version : Produce report on new page with current date



Bobbins
08-12-2016, 09:56 AM
Hello All
This is my first in both trying VBA and the Forum.
so apologies in advance if I get something wrong

I am trying to create a macro that will generate a new sheet with todays date on it
That I can do by using a macro i found online "SheetNameAsCurrentDate"


Sub SheetNameAsCurrentDate()

Dim SheetName As String

SheetName = Format(Date, "dd-mm-yyyy") 'Change the format as per your requirement

Sheets.Add , Worksheets(Worksheets.Count)
ActiveSheet.Name = SheetName
End Sub

then I am trying to copy the contents of a summary sheet minus anything with 0 quantities into the new sheet .
i managed to perform this just once
what i am struggling with is trying to get the name of the new sheet with the current days date selected
however why I try i get the following error

Application.Run "'Stock Status 10-08-16 mark 1.xlsm'!SheetNameAsCurrentDate"
Sheets("Summary").Select
Range("A1:W1").Select
Selection.Copy
Sheets("10-08-2016").Select
ActiveSheet.Paste
Columns("C:C").Select

hopefuilly that makes some sort of sense
many Thanks In advance

SamT
08-12-2016, 10:27 AM
Try this. (Look at the differences between this and your code)

SheetName = "First Part Of Sheet Name_" & Format(Date, "dd-mm-yyyy") & " Last Part Of Sheet Name"

Sheets.Add , Worksheets(Worksheets.Count)
ActiveSheet.Name = SheetName

Sheets("Summary").Range("A1:W1").Copy
Sheets(SheetName).Range("A1").PasteSpecial xlPasteAll
Sheets(SheetName).Range("C1").Select

Bobbins
08-12-2016, 10:45 AM
Hi Sam
thanks for the quick response
Im guessing Im doing something wrong here as Im getting an errorwh
ere the debugger shows in yellow is at the ActiveSheet.Name = SheetName Line

Sub SheetNameAsCurrentDate()

Dim SheetName As String

SheetName = "First Part Of Sheet Name_" & Format(Date, "dd-mm-yyyy") & " Last Part Of Sheet Name"

Sheets.Add , Worksheets(Worksheets.Count)
ActiveSheet.Name = SheetName

End Sub

SamT
08-12-2016, 08:11 PM
That example name I used is just too long. The limit is 32 characters.
Use this 29 character name

SheetName = "FirstName_" & Format(Date, "dd-mm-yyyy") & " LastName"

Paul_Hossler
08-13-2016, 07:12 AM
The limit is 32 characters.

Nitpicky here , but the tab name has to be less than 32 characters