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
easycapitalAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
What is the "3 step macro approach"?
0
Roy CoxGroup Finance ManagerCommented:
The code is unnecessary. Simply format the data as a Table withing the Home Tab of the Ribbon.
Overview of Excel Tables
0
easycapitalAuthor Commented:
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
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Roy CoxGroup Finance ManagerCommented:
It looks to me like the macro is basically converting each row to a column, is that correct?
0
Roy CoxGroup Finance ManagerCommented:
What is the point of the hidden columns? Are they necessary?
0
Roy CoxGroup Finance ManagerCommented:
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
0
Roy CoxGroup Finance ManagerCommented:
This amendment stops screen flicker whilst copying
0_Table_Macro-with-button_dgb--1-.xlsm
0
easycapitalAuthor Commented:
Dear Roy Cox,

Yes the macro is supposed to create a column out of every column in the table in every row.
The only output I am focused on is the one on the second tab, and the last column indicating which week number is not needed.
We can delete the empty columns.
Keep in mind that I want to be able to select the ranges on the fly as the table place and size may change.

I am getting to attached error when I run you re-written code.
I do not know code, but I see the word "error" spelled as "erreor"?
I fixed that word, but then I got another error.

I think that we are on the right track.

Br,
JP
0
Roy CoxGroup Finance ManagerCommented:
Sorry about the typos, last minute amendments before I had to do something else.

Try this
0_Table_Macro-with-button_dgb--1-.xlsm
0
gordonwwaughConsultantCommented:
Easy,
Per your request I'm attaching the original macro-enabled workbook. Please see the Data tab. The colors in cells Z18:AA20 reference the cells being requested by the three input boxes. As a reminder, the macro normalizes the two-dimensional table so the data can be used in a pivot table.

Doug
0_Table_Macro_dgb_color_coded.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
easycapitalAuthor Commented:
Many thanks!
Excellent!
0
easycapitalAuthor Commented:
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
0
Roy CoxGroup Finance ManagerCommented:
Did you try my amended code , it works faster because it does not require four inputs?
0
easycapitalAuthor Commented:
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
0
Roy CoxGroup Finance ManagerCommented:
The range in the code is dynamic!

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

Open in new window

0
gordonwwaughConsultantCommented:
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
0
easycapitalAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.