Link to home
Start Free TrialLog in
Avatar of easycapital
easycapitalFlag for Macao

asked on

EXCEL VBA: Help to understand a previous given solution

Dear experts,

Need help to understand how to use an already provided solution in another question.
https://www.experts-exchange.com/questions/28748719/Macro-to-take-a-table-and-convert-into-a-Pivot-table-ready-table.html

The support requested is:
Please indicate which cells to select in the 3 step macro approach based on the excel file in the above referenced question (and attached excel file as provided as a solution by the expert).

Br,
JP
0_Table_Macro-with-button_dgb.xlsm
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

What is the "3 step macro approach"?
The code is unnecessary. Simply format the data as a Table withing the Home Tab of the Ribbon.
Overview of Excel Tables
Avatar of easycapital

ASKER

Dear Roy Cox,

The I am looking to obtain "one" date column, so for each project we can see
Project 1, RFQ-Est,  Date for this
PRoject 1, date RFQ-Act, date for this
etc

Dear both,

The macro create this in a dynamic manner, whereby it asks for the column headers, row headers and then the data, then it creates in the second tab of the excel. But as I do not know how to read VBA, I can undertand the logic of how to select the ranges for the macro.

Br,
JP
It looks to me like the macro is basically converting each row to a column, is that correct?
What is the point of the hidden columns? Are they necessary?
I have re-written the code and added comments to help you understand what is happening.

First of all on the data sheet the code recognises the data to work with.

I have converted the target data to a proper Excel Table. This is better because the formula will automatically copy down.

The header of the source data is copied to this Table and transposed to a columnar format as is the dates row. The formulas for the dates are updated.

Finally, the code copies the project name into the first column of the Table.
0_Table_Macro-with-button_dgb--1-.xlsm
This amendment stops screen flicker whilst copying
0_Table_Macro-with-button_dgb--1-.xlsm
SOLUTION
Avatar of easycapital
easycapital
Flag of Macao image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Sorry about the typos, last minute amendments before I had to do something else.

Try this
0_Table_Macro-with-button_dgb--1-.xlsm
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Many thanks!
Excellent!
Hi Doug,

I have another excel file open.
Can I also open your file, chose to use macros from all open excel files, select your macro and follow your macro instructions to complete the macro within that excel file?
But when I try to do that, it creates it in your excel file not in the separate excel file where I am trying to apply your macro.

Could you please check from your end and see if it can be modified so that it works?

Br,
JP
Did you try my amended code , it works faster because it does not require four inputs?
Dear Roy Cox,
It needs to be able to address a flexible range.
The four questions are good.
Needing to address my last post above.
Br,
Juan
The range in the code is dynamic!

 Set rRng = Sheet1.Range("AA5").CurrentRegion

Open in new window

Easy,
You can run the macro from the macro enabled workbook (original file) but when you're prompted for ranges you just need to select ranges in the target workbook. I've tried it and the macro will put the data in the output range you select, even in a different workbook. No modification should be necessary.

Doug
Dear both,

I want to thank you for your support, but I am not able to run the code when the table is located in another file.

Could you please each of you open the attach file and be so kind as to write the steps you complete in order to use the macro from your proposed excel file and create the table based on the attached file?

Eagerly hoping to use your solution.

Br,
JP
Book1.xlsx