Toby has attended:
Excel VBA Introduction course
Defining the name of a worksheet as a variable
Hello!
I have worksheets named as strings "DD MM YYYY" which I am trying to reformat and so that I can update a cell with the name from the last sheet in the workbook, in the format DD/MM/YYYY
Using variables is new to me, so I am sure it is something to do with my variable declaration.
The issue seems to be with my LSN variable where I want it to take the name of the last worksheet in my file.
Currently my code is as follows:
Sub UpdateEmailSummary()
'
' UpdateEmailSummary Macro
'
Dim LSN As String
Dim LF As Integer
Dim LM As Integer
Dim LR As Integer
LSN = Sheets(Sheet.Count).Name
LF = Left(LSN, 2)
LM = Mid(LSN, 4, 2)
LR = Right(LSN, 4)
Sheets("EMAIL SUMMARY").Select
Range("C3").Select
ActiveCell.FormulaR1C1 = LF & "/" & LM & "/" & LR
End Sub
Many thanks,
Toby
RE: Defining the name of a worksheet as a variable
Hi Toby
You are so close!!!
LSN = Sheets(Sheets.Count).Name
you missed the 's' off the Sheets.Count which causes the Object Required error
Additionally, you could have used the DATESERIAL function to construct the date from the individual parts (year, month, day)
ActiveCell.FormulaR1C1 = DateSerial(LR, LM, LF)
Hope this helps.
Dennis