Avatar of easycapital
easycapital
Flag 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
VBAMicrosoft OfficeMicrosoft Excel

Avatar of undefined
Last Comment
easycapital

8/22/2022 - Mon
Martin Liss

What is the "3 step macro approach"?
Roy Cox

The code is unnecessary. Simply format the data as a Table withing the Home Tab of the Ribbon.
Overview of Excel Tables
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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Roy Cox

It looks to me like the macro is basically converting each row to a column, is that correct?
Roy Cox

What is the point of the hidden columns? Are they necessary?
Roy Cox

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Roy Cox

This amendment stops screen flicker whilst copying
0_Table_Macro-with-button_dgb--1-.xlsm
SOLUTION
easycapital

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Roy Cox

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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
easycapital

ASKER
Many thanks!
Excellent!
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
easycapital

ASKER
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 Cox

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

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Roy Cox

The range in the code is dynamic!

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

Open in new window

gordonwwaugh

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
easycapital

ASKER
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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23