Excel/macro that will use reference to generate a report
QUESTION: Hi Bob ! How R You ?
I am working with a workbook named "masterdata" which have consumer data in columns A to AT every column have a header having the consumer details. Every consumer is registered with a unique id in column "B".
I need to create different reports with this data where consumer id is always used to identify every consumer and to generate a report.
I want to create a macro program that will allow me to generate these reports and allow me to choose with which header references i want to generate the report and when i enter a unique id in cell b it pull the related data from masterdata which i were chose.
for a example : the masterdata work book sheet1 has data in cell a to AT with different headers...
Suppose i want to create a report with headers having details from column headers c e g h i k and at then the macro will work like..
as i enter a id in column "B2" it pull the data related to the id from column c e g h i k and at and paste the information next to id and continue to do the same for column b.
Kindly reply if its possible. If required i can send you the file
ANSWER: sounds like all you need do is filter by the id so only those rows with that id would show. What am i missing?
---------- FOLLOW-UP ----------
QUESTION: not exactly ! WHAT I WANT IS TO CREATE A MACRO THAT WILL ALLOW ME TO CHOOSE AMONG THE HEADERS TO CREATE THE REPORT RELATED TO SEARCHED ID NUMBER. LIKE IF I NEED TO CREATE A REPORT HAVING HEADERS IN COLUMNS C, D, E, G, H, I,K ETC
AS I CLICK THE BUTTON IT WILL ASK ME WHICH HEADERS DO I WANT TO CHOOSE TO CREATE THE REPORT... THEN FOR EXAMPLE IF I CHOOSE COLUMN C, D, E, G, H, I,K
WHAT IT WILL DO..
1. IT WILL CREATE A NEW SHEET
2. COPY AND PASTE HEADINGS OF CHOSEN COLUMNS WITH HEADINGS STARTING FROM COLUMN "C".
3. WHEN I ENTER ANY ID NUMBER IN COLUMN "B" IT WILL SEARCH THE ID NUMBER IN MASTERDAT AND IF FOUND A MATCH COPY AND PASTE THE RELATED DATA FROM C, D, E, G, H, I,K FIELDS AND DO THE SAME FOR NEXT
THANKS AND REGARDS
Set curr = ActiveSheet
Set rg = Application.InputBox("Select columns you want - if they're not together, hold the ctrl key down", Type:=8)
Set rg = Intersect(Rows(1), rg.EntireColumn)
x = Range("IV1").End(xlToLeft).Column - 2
Range("C2").Resize(100, x).FormulaR1C1 = "=IFERROR(INDEX(Sheet1!R1:R1048574,MATCH(RC2,Sheet1!C2,0),MATCH(R1C,Sheet1!R1,0)),"""")"