VBA Power Query connection

[Kabous07]

Good day

Almost weekend

My experience with VBA is not that good but will try my best. I have done some basics in the past. Im looking to use tables to keep records of projects information and summarize it all on one table. The problem is however, we would have a new table with each new project and i would like to avoid some steps by just using a button linked to a macro. The macro will basically append all the tables to a summary, including the new ones that get daily added.

I have on one sheet a "Summary"("Summary sheet") table and I have another sheet("Template sheet") a "Template" table. We copy the template sheet and rename it as a project number(many different numbers, normally called "PT####"). We then fill in the new project details. Afterwards we would like to add this new project to the "summary table" in the summary sheet. I was hoping to use something like Power Query and VBA or would take any suggestions please. Reason I am looking at PQ is because the append function is very nice in terms of updating project information, when going back to a certain project.

I hope my explanation makes sense.

Kind Regards
Kabous

 

[Fluff]

Cross posted Can PQ have dynamic table names

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[sandy666]

ooops! I see I waste my time so I'll wait for ExcelForum solution

 

[Kabous07]

save file
refresh all
save again
then use

Thank you Sandy. I downloaded it to desktop, opened, saved, refreshed all, saved again, but if i copy the template sheet rename it and insert details in the table, then the summary does not update. Did i maybe miss a step?

 

[Kabous07]

Cross posted Can PQ have dynamic table names

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Apologies, i should have done it for this Forum as well. I am new to this and must say the forums are quite aggressive. I understand that double work could happen and should not have done this.

 

[Kabous07]

ooops! I see I waste my time so I'll wait for ExcelForum solution

It was not my intention to look a gift horse in the mouth. Thank you for your help though, really appreciate the replies. Enjoy the weekend

 

[MARK858]

and must say the forums are quite aggressive

I don't see anything aggressive in what Fluff posted, it just refers you to the rules

 

[Kabous07]

T

I don't see anything aggressive in what Fluff posted, it just refers you to the rules

I should have read the rules, I acknowledge my bad.

 

[sandy666]

ok , here is

s666-PQv3-Project Data.xlsm

Microsoft Excel Workbook

1drv.ms

First time

1. save file
2. refresh all
3. in PQ Editor refresh all
4. save

All the time

1. copy template
2. fill with data
3. save
4. refresh summary table

it's a working file so that's all what I can do for you
have a nice day

 

[sandy666]

or much simpler

s666-PQv4-Project Data.xlsm

Microsoft Excel Workbook

1drv.ms

 

[Kabous07]

Morning all, hope the weekend was good.

I have found a solution(she posted this on perfect timing):