Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Output data from vba as a table


Hello all

Further to my recent question regarding a dymamic list of worksheet names (thanks Variatus for your answers), can someone please now help with how to make a macro output the data as a table.

I have a monthly timesheet workbook into which a sheet is added for each day of the month that is actually worked. as can be seen in the attached sample template, each 'day' worksheet contains rows equal to 15min increments, and columns for time, task undertaken, the contract reference the task related to and notes. Cell A1 on each sheet uses a COUNTA formula to provide the total numer of lines that have actuially been populated for that day and also include a macro to rename the sheet as the date is inputted into cell D1.

On the summary sheet, the main table lists all the current contracts that could be worked on, along with their reference numbers and a SUMPRODUCT(COUNTIF(INDIRECT formula is used to extract the total number of 15min slots have been spent on each contract during the month. Column A contains a dynamic list of worksheet names for this formula to look at but this is where i get stuck as it needs to look at the exact list and errors if asked to include blank rows. I did have the sheet names in a table format which worked fine but then this has to be updated manually.

Can anyone help with how to get the total number of 15min increments spent on each contract number when the number of sheets in the workbook is dynamic?

I won't be upset and don't mind at all if someone wants to start again and completely remake the whole workbook :)



Selected Answer

No. It doesn't need a new start. There are many good ideas in your workbook and I tried to preserve them. However, the handling of named ranges was rather poor, with many duplications, and that affected the functionality of the hyperlinks.

So, I did fix the problem with the sheet list and linked the list to your formula. In the process I needed to sort out the named ranges. I used code to delete duplicates and superfluous names, and that code is still there, still working even while there is nothing to do. FYI, it deletes named ranges at A1 on all sheets, leaving only one which it sets itself - and then deletes and sets it again on each run. So, if you want to give different names to that cell there will be a conflict.

Edit 01 Aug 2018 ============================

At first I suspected your auto-naming, had a closer look at it and didn't like it because it offers no advantage over changing the name non-"automatic" It turned out that it wasn't at fault. Instead, it was the brackets Exel inserts when creating a copy of a sheet, like 01 07 18(2). Brackets can't be used in range names. So I fixed the code and attach the updated version here (previous one removed).

However, your auto-naming procedure would also have problems with duplicate names. So I revamped it. Every dated sheet now has a Change event procedure (before it was SelectionChange and you had to click on D2 to activate it) which fires when you change the date in D1. You can now enter a date in any format, like 1 July 2018 and still get the desired tab name of 01 07 18.

All of these call the Sub RenameTab in the TeachExcel module which does the work. It will give a message if the entry isn't a date or if a tab for that date already exists. It will not rename the tab if it already has the correct name, as your old code did.



Many thanks once again for your answer which works perfectly except for one situation. If you right click a tab and copy the sheet, the macro trips up on the following line if you forget to rename the sheet.
.Range("A1").Name = Tmp

Can this be corrected?
Thanks again
JonP (rep: 37) Jul 31, '18 at 10:02 am
Yes, it can - if Tmp holds a valid range name. Please refer to the edits to my answer.
Variatus (rep: 4889) Jul 31, '18 at 9:22 pm
Variatus, that works perfect thanks very much :)
JonP (rep: 37) Aug 1, '18 at 1:22 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login