When I do automatic things, I like to open and/or to save my report in an automatic way too.
When I use the macro ?
To open and/or to save my report automatically.
How to create the macro ?
Read How to create, edit, hide and select a macro in an excel report
How to create the button to associate it with the macro ?
Read How to create a button and associated it to a macro in an excel report
How is the macro ?
Copy the code below and paste it into your macro. You will see my comments in green if exist so follow the help to adapt to your need.
To open the file in the same folder:
' change namefile by the name of your file
' using wildcard "\name*.xlsx"
Workbooks.Open(ThisWorkbook.Path & "\namefile.xlsx")
To open the file in another folder:
' change path by the full path where to find the file and change namefile by the name of your file
Workbooks.Open("\\path\namefile.xlsx")
With a wildcard, to use only if the file is already open so the macro will include in its code:
Sub test()
Dim i As Workbook
For Each i In Workbooks
' change namefile by the name of your file
If i.Name Like "*namefile.xlsx" Then
i.Activate
Exit For
End If
Next i
End Sub
The wildcard, alias star/asterisk, is useful if the name of your file changes every time but some parts of the name should have some commons. For instance, imagine that your file has a number at the beginning (10-namefile.xlsx) and this number changes every month, so you will use this code. The same thing but for a sheet name:
Sub test()
Dim i As Workbook
Dim sht As Worksheet
' change namefile
Set i = Workbooks("namefile.xlsx")
For Each sht In i.Worksheets
' change namesheet and A2:C20
If sht.Name Like "namesheet*" Then
Range("A2:C20").Copy
End If
Next sht
End Sub
To combine both wildcards, first do the file section then the sheet section or vise versa. To save the file in the same folder:
' instead of this, I can use ActiveWorkbook.Save
ThisWorkbook.Path & "\"
To save the file in another folder:
' to save with another name, change ActiveWorkbook.Name by "namefile.xlsx"
ActiveWorkbook.SaveCopyAs "\\path\" + ActiveWorkbook.Name
When you are managing a team, “how to be a good manager” is the “must”...
As manager, I am doing many reports, even when I was an ITIL consultant, I still needed to do many reports...
ITIL V3 is going to be obsolete...
Managing an IT service when I start a new company is not an easy task, particularly true, if the service...