We help IT Professionals succeed at work.

EXCEL VBA: Help to understand a previous given solution

238 Views
Last Modified: 2017-04-10
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

Martin LissProtect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

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

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

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

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

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
CERTIFIED EXPERT

Commented:
This amendment stops screen flicker whilst copying
0_Table_Macro-with-button_dgb--1-.xlsm
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Roy CoxGroup Finance Manager
CERTIFIED EXPERT

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:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

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
CERTIFIED EXPERT

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
CERTIFIED EXPERT

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions