EXCEL VBA: Help to understand a previous given solution

easycapital
easycapital used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
What is the "3 step macro approach"?
Roy CoxGroup Finance Manager

Commented:
The code is unnecessary. Simply format the data as a Table withing the Home Tab of the Ribbon.
Overview of Excel Tables

Author

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
Ensure you’re charging the right price for your IT

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

Roy CoxGroup Finance Manager

Commented:
It looks to me like the macro is basically converting each row to a column, is that correct?
Roy CoxGroup Finance Manager

Commented:
What is the point of the hidden columns? Are they necessary?
Roy CoxGroup Finance Manager

Commented:
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
Roy CoxGroup Finance Manager

Commented:
This amendment stops screen flicker whilst copying
0_Table_Macro-with-button_dgb--1-.xlsm
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
Roy CoxGroup Finance Manager

Commented:
Sorry about the typos, last minute amendments before I had to do something else.

Try this
0_Table_Macro-with-button_dgb--1-.xlsm
Consultant
Commented:
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

Author

Commented:
Many thanks!
Excellent!

Author

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
Roy CoxGroup Finance Manager

Commented:
Did you try my amended code , it works faster because it does not require four inputs?

Author

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
Roy CoxGroup Finance Manager

Commented:
The range in the code is dynamic!

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

Open in new window

gordonwwaughConsultant

Commented:
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

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial