Need VBA or macro for Dynamic Report Creation with specfic Criteria

[Pankajlko]

Hello All

We know about this website/forum to my friend.. he suggest me we put our problem here so we will got a VBA solution for my main reporting problem..

We are working a construction firm where we have a lot no of road database in excel file with various type of condition..

We make report mostly three type currently we make report with filtering, sorting etc methods and after that we format this data as per reporting..

1 Block wise Report (we want any specific block all road data) (Block name in Column B)
2 Sanction Project wise Report (we want all road data in specific Sanction Project) (Sec_project Name in Colum H)
3 Road Condition Report (we want all road data in specific Road Condition) (Condtion_road Name in Colum G)

Our reporting format is common i.e. 7 Column. (Sheet 2)

We require a VBA Macro for dynamic report creation.. if we enter any one of three Search place we want a filter report in 7 column reporting format with Search Criteria..

Currently some one told me to record macro we record a macro but with this record macro not exactly work as per my reporting..

Reporting Style :-

• First we Fiter Data from main sheet as per required column..
• Then we sort Filter data on Column G i.e.Sec_projecte Name
• Then We numbering start from 1 to filter data in Report Sheet Sr. No.
• and in last we insert a Total line with Project Name and sum these project road lenght and Sanction Cost
• and finally Grand total of all totals.

i think we clear my reporting pattern for VBA or macro creating..
We want when we Enter specific block name in B2 Cell then That Specific Block report display in reporting format....
if we enter Speific Project name in D2 then Specific Project report display in reporting format...
if we enter Speific Condtion of Road in G2 then Specific Road Codntion report display in reporting format..
My Data (this is just 300 road data and it's not original data of my database) in Main Sheet Reproing Sheet is Sheet 2
in Sheet 3 we give a exmple that which type we prepare report mannually..

My Recorded Macro is already apply in File

Hope We got my problem solution with this forum..

Thanks in Advance..
Pankaj

[Pankajlko]

I need this help...Pls

[kasan]

Does all your files contains columns with the names exactly the same as columns in Report? If the answer is Yes, than I think it`s quite easy solution, VBA arrays shoud do the trick.
And report on Sheet3 - is the result you want to recive, right? Is this report form constant or there are other reports form? Didn`t really get how much reports you need to prepeare one or three or ..?
If there are any other forms - please attach them.

[IonutC]

mate, i suggest splitting the tasks into different queries...

[Pankajlko]

Hi Kasan sir
Yes my files contains columns with the names exactly the same as columns in Report
and sheet3 is my report output example for easy to understand..
We want one time one report.. but my search criteria is three type.. but report output is common..

Thanks for showing intrest to my problem..

[Pankajlko]

IonutC sir Thanks for showing intrest in my thread ... sir how's possible to split this task in different quires .. actually we want a single macro so that will be very easy for me..

[kasan]

Pankajlko, please check attached file.
On sheet "Settings" in cell B4 you can fill any Block Name (assuming that this Block exist in sheet Main), then press button and get result on sheet "Report"
This is just temporary result, if direction of my idea is right - than we can develop it (add some formating and so on)

[Pankajlko]

Hi kasan its work exactally as per my one search need..
actually insted of run report button and in Cell Value B4 we enter text in cell b2 then automatically result display in same sheet not report sheet..

ur current VBA is running in only Block name...

thanks for ur valuable help..

we also add rep..

thanks

[kasan]

Well, as I told you - this is just as demonstration.
That do you mean by "VBA is running in only Block name"? It means you need a report with different criteria, right?
Current code can be copied and slightly changed to make him do what you wanna do. Just give me examples of other reports.

[LeoTaxi]

Please Login or Register  to view this content.

Kind regards
Leo

[Pankajlko]

Hi LeoTaxi Sir ur provided VBA is working suparb as per my need..

Thanks a lot

sir is it possible in line sum show in value form if show formula insted of value the that will be very usefull to me..

and if any option of search criteria is not found in main record then a msg box display that "NO RECORD FOUND"

currently it's vba running as per my need thanks u so much..

Really this forum is very usefull to all of us..

Thanks

[LeoTaxi]

For the msgbox

Please Login or Register  to view this content.

no soluution for formulas instead of values, maby manual ?

Kind regards
Leo

[Pankajlko]

no problem about formula issue.. i think my problem will be solve with this VBA thanks a lot