Dear Experts,
I need to create a report in excel.
please see my attached worksheet for detail.
if it possible please make a code for the same.
regards,
Roofi
Dear Experts,
I need to create a report in excel.
please see my attached worksheet for detail.
if it possible please make a code for the same.
regards,
Roofi
Abdul Rouf Roofi
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:
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:Please Login or Register to view this content.
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):
The end result is the interactive table you wanted minus the extra clutter.Please Login or Register to view this content.
A different solution but I think cantosh's is better i.e. hiding the columns you don't wish to see.
AlfPlease Login or Register to view this content.
Thanx buddies for your support,
actually question is not to see some fields and not the others.
i try to explain my question again.
I need to generate a report based on first table in my sheet.
start month is July
if i assume the current month is Sep then
report should be monthwise all fields starting from Jul upto current month with their respective totals at the end.
i can say it is accumulative report alongwith monthwise detail upto current month.
please review.
regards
Roofi
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
Dear ALF
Sorry! i could not understand this code. your code gave me 90% solution.
I need this report to be generated in new sheet.
please review.
regards
Roofi
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.I need this report to be generated in new sheet.
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
Thanx dear ALF
i am attaching my original workbook.
SR = is my current sheet
Actual-A = is data source.
being in SR i want to extract data from Actual-A on same grounds (but based on Current Month = "R1")
and show it in new worksheet as you explained in your last reply.
report button is to run same macro.
please review.
thanx
Roofi
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
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.your code gave me 90% solution. I need this report to be generated in new sheet.
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
Dear ALF
Wow great!
its working perfect.
ok next time i will take care as you commented
regards,
Roofi
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
Dear ALF
Support from your end was fantastic.
i have one more question please.
i m attaching my worksheet again. please prepare report as i added.
Amount from Actual-A
Number from Actual-B
Total should be only of amount
regards,
Roofi
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
I WILL BE ANXIOUSLY WAITING
REGARDS,
rOOFI
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 codejust before the "End Sub" line.Please Login or Register to view this content.
Alf
Wow
That's great.
You are genius. will touch you if i need any help in future.
Thanks a lot.
Roofi
You are welcome.
Alf
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks