Automating generation of multiple tabs in excel based on data records from dRofus Modified on: Mon, 7 Jan, 2019 at 10:00 PM

Very sophisticated formatted multipage reports can be produced through the use of custom excel template exports from dRofus. 

The workflow described below describes how you can automate, via macros and formulas, the generation of multiple Room Data Sheets (as an example) from data exported to a worksheet in excel.


1.     Firstly, as you will be using Macros you need to enable the "Developer" menu in Excel. File→Options→Customise Ribbon



2.     Also enable macros and trust access to the VBA project object model



3.     Set up named Tabs for your dRofus exported data and for the Formatted Report that you want to replicate. Basic examples below:





4.     Insert the following formula into the "SpaceID" value field, this will use the name of the Tab as the displayed value (this is to be used as the unique identifier to differentiate the tabs/sheets

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)


5.     In the fields below use the following VLOOKUP formula (or similar) to populate the values based on the Unique Identifier and the associated records in the table on "MasterData"

=VLOOKUP(B5,MasterData,2,FALSE)


6.     Next you need to create a few dynamic named ranges using the "Name Manager" under "Formulas". The named ranges need to be dynamic as the number of records exported from dRofus will vary each time the report data is exported



7.     To create a Named Range for all of the dRofus exported data the OFFSET function can be used to define a range of cells based on only those cells that contain values. For the shown example the following formula will select the entire data table including headings:

=OFFSET(MasterData!$A$3, 0, 0, COUNTA(MasterData!$A:$A), 15)




8.     There also needs to be a dynamic named range for the unique values:

=OFFSET(MasterData!$A$4, 0, 0, COUNTA(MasterData!$A:$A)-1, 1)




9.     Now that the named ranges and Report form have been set up we get to the fun bit, the macro. I have created a new Macro called "SplitFilterSheet"




10.     The Code Looks like this and all it does is look at all of the values in the named range "SplitCode" and creates a copy of the "SpaceDescriptionForm" tab and names it the same as the unique value in "SplitCode". The formulas and VLOOKUPs in the "SpaceDescriptionForm" table take care of populating the values from the master data :

Sub SplitandFilterSheet()
'Step 1 - Name your ranges and Copy sheet
'Step 2 - Loop until the end of the list

Dim Splitcode As Range
Sheets("SpaceDescriptionForm").Select
Set Splitcode = Range("Splitcode")

For Each cell In Splitcode
Sheets("SpaceDescriptionForm").Copy After:=Worksheets(Sheets.Count)
ActiveSheet.Name = cell.Value

Next cell
End Sub


11.     The end result looks like this and if the page was defined in the original tab then these can be printed or PDF'd:




12.     As a final touch you can add a button to run the macro: