Excel tutorials

2. Creating a Database for All Company Vehicles (2019-sumarum.xlsx)

Continue tutorials from the previous web page. In this section of the tutorial, the next step is to show the following steps to take for the organization of keeping track of the vehicle traffic records in the company. The idea was to include all vehicle registrations and their associated data in an Excel workbook (located in the 'root folder' for a given year).

From this workbook we will pull all vehicle data into other workbooks that are in sub-folders for the corresponding month of the year. Also in this workbook we will include the number of years in which we keep records. This number later, we pull out all the workbooks. Why did I just so imagine you will see after studying the complete tutorial.

So our workbook is in the "2019-year" folder (see previous tutorial).
This workbook has been named "2019-sumarum.xlsx" and contains a worksheet listing all the data on the vehicles. Of course, you can add more worksheets and data as desired.

The database on motor vehicles in the company

3. Creating Templates for Every Traffic Vehicle (template.xlsm)

We come to an important workbook that we will later copy, using the VBA macro copy 100 times, and name it from the list on the worksheet. In this way we will create 100 workbooks and each workbook will have the same name as the registration mark for each vehicle. Afterwards, we will copy all 100 workbooks in each sub-folder for the other months of the year. Here we need to be very careful, ie consider what data we will put into this workbook. After making and duplicating 100 times, there is no return back if we notice that we have made a mistake. I'll give you a simple example here and you think deeper about all the information you need. This workbook 'template' was taken in XLSM format (because I used and still use VBA macros)

My idea is to create a workbook as a template in which the first Sheet1 workbook will be renamed to the vehicle registration number. In the end, we will have 100 Excel files that are known as vehicle registration code as well as the first working-list will be called vehicle registration code. Maybe you're wondering why I figured this out. I just wondered if my boss might have at one time wanted to have all the 100 Sheet Tabs in one workbook and that each workbook be with the vehicle registration number. So that he could do it in advance to plan possible situations. Also in this book I will retrieve the necessary information from the above-mentioned workbook "2019-sumarum.xlsx". I think this is a good organization in case some changes are made to a particular vehicle, so these data changes in one workbook and all other workbooks will pull the modified data.

The steps we need to do to successfully create a workbook template that we will copy or duplicate 100 times later, but with the names from the list.

  1. The organization of data on the first worksheet "Sheet1 'which will later be renamed in the vehicle registration number.
  2. The organization of data on another worksheet 'Sheet2' which we can appoint as 'City1'
  3. Organizing data on the third sheet 'Sheet3' that can be named 'City2'
  4. The fourth worksheet 'Sheet4' will serve as a help which will make additional data organization and the results that we need for a final report to the boss. This worksheet will be named 'helper'.
  5. And finally, the fifth worksheet 'Sheet5' will be only temporary and will serve us for duplication workbook 100 times in the same folder.

So the base workbook (template for creating duplicates from the list) might look like this in the picture below.

1. Organize the data on Sheet1 (this first worksheet will be renamed to the registration number of the vehicle so that it has the same name as the workbook, I plan to do all this in a single step on all books through the VBA macros). On this worksheet I made a certain automation and checked whether the name of the 'Sheet Tab' is the same as the file name. If not, then Conditional Formatting will alert you. Also in ThisWorkbook Module I set up a VBA macro that automatically refreshes all data when you open it.

Copy this VBA in ThisWorkbook Module

Private Sub Workbook_Open()
'refresh all formulas after open a WBK
ActiveWorkbook.RefreshAll
End Sub

 

Copy this VBA in Sheet4(helper) Module

Private Sub Worksheet_Activate()
'refresh all formulas after activate sheet
ThisWorkbook.Worksheets("helper").Range("A1:AC1000").Calculate
Range("B1").Select 'change to suit
End Sub

This looks like Sheet1 (vehicle registration and in this case GA001AK). The data in the worksheet is for example only in this tutorial.

Sheet1 and basic data in the organization

2. Organize the data on Sheet2 I leave you here, set up the information you want

Sheet2 and basic data

3. Organization of data on Sheet3 is identical to Sheet2

Sheet3 and basic data on it

4. The 'helper' working list that serves as an auxiliary work sheet on which we consolidate certain data. From this worksheet, we retrieve the data to the first main work sheet (in this case it is GA001AK).

Helper sheet

Here in the tutorial I will not explain which of the formulas I used. It can be seen after you save the ZIP file to your computer (link at the bottom of this web site). Excel file is XLSM format. If you do not trust someone for your security, press SHIFT and then double-click the file name. This will prevent VBA macros from launching. Also, do not be surprised if you have a warning about the links (though I did this on my computer at 'D' partition). Simply block links and warnings if you like. You will not be able to see the results but you will be able to see formulas in the comments of certain cells.

On the 'helper' worksheet, there are formulas for automatic retrieval of the number of years (from the workbook '2019-sumarum.xlsx' at the beginning of this tutorial) and an automatic count of the number of months as well as automatic verification, whether the first worksheet has the same name as well as the workbook name (A14 cell).
Also, all dates in the given month are automatically changed and all depends on which month you are using the relevant workbook.

4. Copy, duplicate or create multiple workbooks from the list in the 'A' column

Above the displayed image and the mentioned texts refer to a finished workbook, ie how the workbook should look after its completion and certain activities in this organization. We continue to the fifth working list. In this worksheet I will list a list of all registration numbers for all vehicles. From this list I will create multiple worksheets that will be named in the list 'A' column names.

This is how the Sheet5 worksheet looks like (this worksheet will be deleted in multiple workbooks in the sub-folder later)

Duplicate a workbook template 100 times

 

Copy this VBA macro below, copy it into standard Module1, workbook 'template.xlsm'.

Option Explicit
'this Module1 should be deleted after creating all WBKs in the list
'published by www.ic-ims.com
Sub CreateMultipleWorkbookFromList()

'published by FOX028 ES
Dim Opseg As Range
Dim Polje As Range
Dim Odrediste As String
Dim Original As String
Dim Poslednji As String

'Optimize Macro Speed
Application.ScreenUpdating = False
Application.EnableEvents = False

Original = ActiveWorkbook.FullName
Odrediste = "C:\Temp\ccc\" 'change to suit
Set Opseg = Range("A2:A101")

For Each Polje In Opseg
'save as xlsm file format
ActiveWorkbook.SaveAs Filename:=Odrediste & Polje.Value & ".xlsm", _
FileFormat:=52, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Poslednji = Polje.Value & ".xlsm"
Next
Workbooks.Open Original
Workbooks(Poslednji).Close

'Reset Macro Optimization Settings
Application.EnableEvents = True
Application.ScreenUpdating = True
'Message Box when tasks are completed
MsgBox "Task Completed!"
End Sub

After completing the VBA macro, you created multiple workbooks named from the list in the 'A' column, this looks like this below. Note that I did the last thing in C:\Temp\ccc\ path.

Created multiple workbooks, from the list in column 'A'

Next is the continuation of other actions, which is to rename Sheet1 in the workbook name, in one step on all 100 books. also follows the deletion of Module1 in all workbooks in the folder. I will do this work in one step.

And at the end of this first part of the tutorial, you can Download to your computer's ZIP on which I did this tutorial.

List all topics related to complete tutorials

  1. Creating Top Folders and Sub Folders in One Step
  2. How to copy a workbook 100 times and create multiple workbooks from the list in the 'A' column
  3. Rename the first in the order of the worksheet, in multiple workbooks in the same folder
  4. How to remove the last Worksheet in multiple Workbooks in the same folder
  5. How to remove a particular VBA module in multiple workbooks in the same folder
  6. How to save all workbooks as values without formulas in all sub-folders

Click to continue tutorials: ------ >>>>>> The next part of the tutorial Item 3 from list above