List "Defined Names"

List Defined Names

If you are like me, when you finish the project you will be lost with all the components of your dashboard, more importantly, when a change needs to be perform you need time to study the dashboard again, or, your colleagues will be lost, in particular when you build the dashboard using “defined names”.

Now, how do you keep track of all these names?

Thankfully, there is just the feature for this, called as “paste names“.

This is how it works.

·         Insert a new worksheet

·         Press F3 (Paste Name dialogue will be displayed)

 

·         Click Paste List

Eureka!!

VBA saves as filename plus add date to filename

Process to save a file with a specific name and add the date to the filename


Dim strDataPath As String

strDataPath = "C:\YOURLOCATION"

ActiveWorkbook.SaveAs FileName:=strDataPath & "CI Services Workstack Automation Data" & "-" & Format$(Date, "DD-MM-YY") & ".xls"

' ~~~ strDataPath is your location where the file is to be saved

Process to save the current filename, and, add the date to filename

Sub MyFileSaveAs()

' File needs to be saved as a xlsm

            Dim last_dot As Long

           last_dot = InStrRev(ActiveWorkbook.FullName, ".")

           Dim Name As String

Name = Left$(ActiveWorkbook.FullName, last_dot - 1) & "-" & Format$(Date, "DD-MM-YY") & Mid$(ActiveWorkbook.FullName, last_dot)

        ActiveWorkbook.SaveAs Filename:=Name, FileFormat _

    :=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

End Sub