How to create report through vb script using an excel data source

My advice would be to stick to your main table and simply hide the columns/months that aren't necessary. Extend your data to include a 'Totals' column in P2:P8, then create a module and add the following function:

Please Login or Register  to view this content.

This brilliant UDF (not written by me) will return only the visible subset of cells, so put the function below in P3 and fill down to P8:

Formula:

Please Login or Register  to view this content.

Finally, use the procedure below to adjust the table based upon what month you put in cell C11 (I'd use data validation on C11 to limit responses, but that's your call):

Please Login or Register  to view this content.

The end result is the interactive table you wanted minus the extra clutter.

A different solution but I think cantosh's is better i.e. hiding the columns you don't wish to see.

Please Login or Register  to view this content.

Alf

Reading your comments I think you have not tested any of the proposed macros so I'll upload your original file with my macro.

To test change month value in cell C11 and run macro "ExtrData"

Alf

I need this report to be generated in new sheet.

That should not be any problem if I know what you mean. One option I could think of is that when you run the macro this adds a new sheet (tab) to the excisting file and the sheet name will be the value that C11 contains and it will contain all data from the specified periode, i.e. if C11 value is "Sep" then sheet "Sep" will be added with data from "Jul", "Aug", "Sep" and the "Total" column.

Specify what you wish or you can upload a file to show what the result should look like then I probably could fix it.

Alf

Here is the modified file. Running the macro it will pick up the month from sheet "SR" cell R1

It will then copy the report range based on the month and the number of rows in sheet "Actual-A" by checking this in the A column.

The macro will then add a sheet, sheet name is the cell R1 value and paste the copied values to this sheet.

Doing a rerun of the macro, macro starts by checking the visible sheets and delete the sheet whose name is not "SR" or "Actual-A" before it builds the new report.

Some general comment i.e. when asking for help do give all the relevant information.

In you first comment you said my macro was

your code gave me 90% solution. I need this report to be generated in new sheet.

How was I to know what you wanted? You did not specify your needs. Further more working with your file I discovered 4 hidden sheets in your workbook. This is also something you should have told when uploading the file.

So think about giving as much information as possible the next time you aks for help. This will make things so much easier for those who wish to help you.

Alf

Glad you liked it. And yes give as much information as you can. Uploading the file you work with is a good idea but remove sensitive information before uploading and also do a bit of testing before you "go live" as even if members do their best we sometime makes mistakes so testing and seeing you get the result you know are right is a good way of checking.

Thank for feedback and rep

Regards

Alf

Well it look interesting so I'll have a go at it. 2 - 3 days probably to finde the best way to do this.

Alf

Ok here is you updated file. As this is contains a macro "build to order" it means that you can't change the layout of sheet "Actual-A" and "Actual-B" as this may cause the macro to stop working properly.

In the uploaded file the calculation mode was set to "Manual". The macro changes this setting to "Automatic" as this is needed to sum the "Totale" amount.

If you wish / need to turn calculation to manual add a this line to code

Please Login or Register  to view this content.

just before the "End Sub" line.

Alf

You are welcome.

Alf