Hi,
I have created a dashboard which has the data from 2009 till 2015 with actual and forecast numbers. I have the raw data in the excel sheet and in the main sheet, I have a combobox with the list of the departments. Once I select the department, it displays the respective metrics and data for all the months. The values are picked up from the raw data sheet with vlookup functions. And I also have a summary sheet which lists all the departments with the respective metrics/data shown in another sheet which is populated with a VBA macro which I have coded. There are around 70 departments for which the macro has to run to generate the summary sheet.
The problem is it is taking a lot of time to run the macro. It takes around 15 to 20 mins to run the macro and update the sheet.
Also, I have a second summary sheet which has again a lot of metrics shown in a different view and the data for this is from the raw data sheet through vlookups.
If I run the macro to generate the first summary sheet along with the second summary sheet in excel, it takes around 1 hour to completely run the macro and update. So, to save time, I make a copy of the file and delete the second summary sheet and run the macro to update the first summary sheet and then copy this into the first main file.
I have kept the calculation in excel as automatic so that all the formula updates happen automatically while running the macro.
Is there anyway to cut down on the macro run time. Since I am using 'iserror' statement with 'vlookup', there were multiple 'vlookup' which I thought is slowing down the macro and hence I have used 'vlookup' at the end of my data columns and then referring these cells in the 'iserror' statement. But still, it didn't help much to reduce the macro run time.
Can anyone help me with this or should I have to redo the whole dashboard with a different approach. It would be helpful if I get some quick response.
Thanks in advance !
Vish
I have created a dashboard which has the data from 2009 till 2015 with actual and forecast numbers. I have the raw data in the excel sheet and in the main sheet, I have a combobox with the list of the departments. Once I select the department, it displays the respective metrics and data for all the months. The values are picked up from the raw data sheet with vlookup functions. And I also have a summary sheet which lists all the departments with the respective metrics/data shown in another sheet which is populated with a VBA macro which I have coded. There are around 70 departments for which the macro has to run to generate the summary sheet.
The problem is it is taking a lot of time to run the macro. It takes around 15 to 20 mins to run the macro and update the sheet.
Also, I have a second summary sheet which has again a lot of metrics shown in a different view and the data for this is from the raw data sheet through vlookups.
If I run the macro to generate the first summary sheet along with the second summary sheet in excel, it takes around 1 hour to completely run the macro and update. So, to save time, I make a copy of the file and delete the second summary sheet and run the macro to update the first summary sheet and then copy this into the first main file.
I have kept the calculation in excel as automatic so that all the formula updates happen automatically while running the macro.
Is there anyway to cut down on the macro run time. Since I am using 'iserror' statement with 'vlookup', there were multiple 'vlookup' which I thought is slowing down the macro and hence I have used 'vlookup' at the end of my data columns and then referring these cells in the 'iserror' statement. But still, it didn't help much to reduce the macro run time.
Can anyone help me with this or should I have to redo the whole dashboard with a different approach. It would be helpful if I get some quick response.
Thanks in advance !
Vish
