We help IT Professionals succeed at work.

Macro to convert table into pivot tables

412 Views
Last Modified: 2017-05-14
I had this question after viewing EXCEL VBA: Help to understand a previous given solution.

Dear experts,

Please apply the VBA code solution to this question into uploaded excel file labeled "FILE-1".

Using similar methodology used by Roy Cox in attached file #2 (usage of dynamic rage solution) which contains a macro called "ConvertData", with no need of further user interaction more than just simply pressing a button which triggers the macro, then the following should happen:

1. In the tab "Table Data" (note that this tab is hidden, and would like to keep it so), it:
1.(a) Deletes all data below headers "Project Commment, Raw Data", then
1.(b) updates the table below headers "Project Commment, Raw Data", with the data from "Input data sheet".

2. Update excel tab "Journal update" with the name of the user shown to be using the excel file at the time the macro botton is pressed, keeping a journal of all the times this has been updated and keep adding in the first row, the next update time which this macro was updated.

3. "Refresh" all pivot tables in the worksheet.
3.(a) Will this macro as you send it now "refresh" even new pivot tables that are added to the excel file without needing to modify the macro you propose?
3.(b) in the column label of the pivot table (as an example, see cell B13 of excel sheet "Report-All activities"), hide all weeks which are older then two week from current week number (week starting on Mondays) - meaning unselect all number which are less then. If it helps, I have current week calculated in cell R3 of excel sheet "Report-All activities" (but you would need to add a range in case this figure is moved somewhere else). Please apply this to pivot table in excel sheets: (a) "Report-All activities", and (b) sheet "Report-Per activity".
3.(c) then, for sheets "Report-All activities", and "Report-Per activity":
3.(c).1 highlight the entire column, which corresponds to the current week. I have added a "hidden" helper row for this part, in row 1 of sheets "Report-All activities", and "Report-Per activity".

4. To easily  confirm to the user that the macro has run, please add date a stamp in cell A1 of sheet "Input Data Sheet stating  "The reports have been last updated: " name of the user and plust the date/time.

Br,
JP
File-1.xlsx
File-2.xlsm
Comment
Watch Question

Roy CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
My code converted the raw data to a table of data that could be used in PivotTables. Your file #1 does not apper to have similar raw data.

The hidden sheet contains daat that could be used in PivotTables. I'm not sure why you need a macro to create PivotTables when you have them already. I think you only need a button to refresh the PivotTables

Author

Commented:
Dear Roy,

File 1 above, does not contain any macros.
I pasted the info by using your ConvertTable macro.
Orange for headers, blue for rows and grey for data.
But I need to be able to work with a dynamic range in order to be able to have a macro select all this for the user.

See just added sheet "2017 05 06".

But I need the solution in File 1, as indicated in my first post above.

Thanks!

Br,
Juan

Author

Commented:
uploading file 2 for my comment above.
File-2-v1.xlsm
Roy CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
Juan

The my code does use dynamic ranges. CurrentRegion is dynamic

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

Open in new window

Author

Commented:
Dear Roy,

I have not been able to apply your dynamic range solution.

Could you please for this question, apply to file 1, the items I am asking for in my initial request above.

Otherwise, I am stuck with not having a solution to what I am trying to resolve.

Br,
JP
Roy CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
I'm working on this but there's alot to do and I will not finish it tonight

Author

Commented:
Excellent.
I will review as soon as I see your post.
I am following this one closely.
I am in Easter US time + 1 hour.
Thank you Roy!
Neil FlemingConsultant and developer
CERTIFIED EXPERT

Commented:
I took a crack at what you want. I am not entirely sure that your "table data" worksheet is necessary, but haven't second-guessed you.

The attached file does I think what you want
1. It deletes the contents of the table in "table data"
2. It copies all data from the table in "input data sheet" to that table
3. It refreshes every pivot table it can find in the workbook
4. Where a pivot table contains a "Week #" field, it filters the field so that only weeks later than "currentWeek-3" are displayed
5. it updates the journal worksheet
6. It flags the last update on the input data sheet

re dynamic ranges: because your data is stored in excel tables, these expand and contract dynamically in any case as you add data to them. So I just accessed the "DataBodyRange" and "HeaderRowRange" of the tables.

Hopefully I haven't just duplicated some work Roy is doing for you, but was working on a similar pivot issue, so this did not take a huge amount of time. His solution may well work better than this... it seems a little bit slow.

You'll see in the Table data sheet that I have hard-coded the week number using VBA code, and also hard-coded the 0s and 1s, rather than embedding the complex Excel formulae you had. Hopefully that is OK. Your formula seemed to be returning week "52" for some blank cells in places.

Author

Commented:
Dear Neil,

I do not see any file relating to your post above.

No need for the data table, if you say that it is not needed. But good to have so I can use the macro for other solutions, so that it not only show columns with dates.

I added the formula for 1 and 0s in order to be able to filter maually in the pivot table which columns I do not want to include.

I am eager to review what you have prepared.

Br,
JP
Neil FlemingConsultant and developer
CERTIFIED EXPERT

Commented:
Oh.. Apologies. Attaching again.
File-1.xlsm
Roy CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
You have actually changed things since I provided the original macro. I have changed the code to suit the new Table, let me know whether to continue. I've just got home and have plenty to do.

If you want me to continue clarify what data should be transferred to the new sheet
File-1.xlsm

Author

Commented:
Dear Roy,

The data table is showing blank in your excel, that data should be filled out with the items i mention under section 1 in my first post above.
I have created a table, in the input sheet, that table is used to create the table in sheet "Table Data".
You mentioned in previous solution that your solution was dynamic, so bringing the data from the input sheet to Table data should work.

Then next steps requested are detailed above. Just let me know if I need to provide more detail on any of them.

Dear Neil,

I just reviewed the last excel file above, and it seems to be working fine, just that I noticed something:
I added an apri 24, 2017 value (week 17) into cell O7 in Data Input Sheet.
Please see cells C16:K16 of sheet Report-All activitie, and this does not show the week in incremental order, e.g. week 17 shows up after week 16.

I am attaching your file upated with this update in the date, in order to show you what I see.

Br,
JP
File-1-Neil.xlsm
Roy CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
As I said, you appear to have changed the data layout and I want to know what wants importing.

The code that I have added so far is concise and efficient i can add the other features but I want to know what you want moving.
Neil FlemingConsultant and developer
CERTIFIED EXPERT

Commented:
JP -- the issue you note caused by adding a new week relates to column sorting. I added an autosort to the attached which fixes the issue.
File-1-Neil.xlsm

Author

Commented:
Dear Roy,
Neil has almost finished with this question.
Thanks.

Dear Neil,
Any chance that Journal upates can sort from most recent to oldest. Top to bottom?
Also, the week # seems to be starting from Sunday, could you please make it start from Monday?

Br,
JP
Neil FlemingConsultant and developer
CERTIFIED EXPERT

Commented:
Here you go. Journal updates in descending order, and weeks starting on Monday.
File-1-Neil.xlsm

Author

Commented:
Dear Neil,

I have taken some time to respond in order to understand why I am getting the following error when running your code, with real data.
"Run time error 1004':
Unable to obtain the WeekNumber property of the WorksheetFunction class."

I know nothing about VBA but I deduce that the issue coming up is because for some reason excel is having problems identify what is the corresponding week number.

Could you please:
1] remove from you code having to come up with the data for columns [Week #] and [Date for the PT] in the table in sheet "Table data"; and
2] add add a formula within the table under Week # to calculate the week number based on the respective row under column [Date Raw] and if error = ""
3] add back formula within the table under Date for the PT to calculate the equal 0;  if no # above 0 appear in the same row under Week # column, otherwise the result = 1

With this we would achieve that:
1] I can manage errors easier by hand by using formulas and we use the macro to do the part I cant do; which is creating columns [Project], [Comment] and [Date Raw].
2] Maybe the macro works faster because it needs to perform less calculations.

The actual table has about 75 rows!

Many thanks!

Br,
JP
Neil FlemingConsultant and developer
CERTIFIED EXPERT

Commented:
Hmm. Yes. The "weeknum" function is not Excel's finest invention.

It returns a value if it is applied to a blank cell, for instance. And clearly something in your full workbook is triggering an error in the VBA version of the function, despite my best efforts to protect it from doing that.

I've put formulae back as you requested in the attached version. Less complicated than in your original, but they work. Note however that the formulae are being WRITTEN into the worksheet by the VBA. In other words if you change the formulae manually and then hit the update button your changed formulae will be over-written.

If that's no good, let me know and I'll think of another way of clearing the data from worksheet "table data" while leaving the formulae in place.
File-1-Neil-v2.xlsm

Author

Commented:
Dear Neil,

In reference to sheet "Table Data".

Yes, please leave the formula in the table outside of the VBA, I think the formula will work just as well and I con modify this as I need in the future. The formulas you have created create a result of "" in no dates apply so it does not hurt to leave them plus been part of the table, they will copy themselves as the table expands.

In addition, I notice that only the word project and Milestones show up in column [Comment]. Please allow for all header names to show up, otherwise my real column names are not showing up.

Br,
JP
Neil FlemingConsultant and developer
CERTIFIED EXPERT

Commented:
Do you mean ALL header names, including those columns named ".12", ".13"? Or excluding those?

Author

Commented:
Dear Neil,

I need all headers from the input data sheet which are within the table.

They all need to show up in the [Comment] column of the sheet called "Table Date".

Br,
JP
Neil FlemingConsultant and developer
CERTIFIED EXPERT

Commented:
So.. like this? Revised excel file attached
data table

Author

Commented:
Dear Niel,

No excel was attached.

Br,
JP
Consultant and developer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Excellent solution!!

Many thanks!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.