it's all about - DYNAMIC

[suby]

All,

I have 3 macros in Place

Macro 1 - Read the sheets from excel workbook.

Macro 2 - Once i read a Particular tab I'm transposing the Data using Record ID, USER ID , USER FOLDER

-- The transpose works fine for the first three tabs since the column headers are same.
-- when i want to read the sheet 4 the column headers are different so i wanted the transpose to be dynamic to use M_ID and FILE ID
-- when i want to read the sheet 5 the column headers are different so i wanted the transpose to be dynamic to use F_ID and FILE ID

Macro 3

I'm using a summarize tool where i wanted the summarize to be dynamic based on above.

Attached the Sample Data.

[Peachyco]

My instinct is to introduce a flag in Macro 1 that tells Macros 2 & 3 how to treat the dataset.

For example:

1. Macro 1 introduces the "Flag" column, like 1 for USER_ID, 2 for M_ID, 3 for F_ID
2. Macro 2 checks the value of Flag, and Transposes accordingly. Maybe use a Filter Tool to check.
3. Similar to Step 2, Macro 3 checks the value of Flag, and Summarizes accordingly.

[suby]

Hi,

Could you please elaborate on the first step.

1. Macro 1 introduces the "Flag" column, like 1 for USER_ID, 2 for M_ID, 3 for F_ID

[suby]

All,

Any suggestion on how to build a Dynamic Transpose.

When sheet 1 i have to use two columns in my Transpose 

When sheet 2 i have to use two different columns in my transpose..

Thanks

[terry10]

In answer to your dynamic transpose questions, you can check "Dynamic or Unknown Columns".

[suby]

Hi Terry,

Thanks Already   "Dynamic or Unknown Columns". is checked...

As mentioned in my original post in my workflow I'm reading the sheets one by one  using a Macro then i have the transpose using the 

USER ID and USER FOLDER  say these columns are same in first  three sheets which is ok but

when it read the sheet 4 i want the transpose to use M_ID and FILE ID

when it read the sheet 4 i want the transpose to use F_ID and FILE ID 

Thanks

[Luke_C]

@suby Would be helpful to include the macros/workflow you already have so we can see what you're working on.