VBA Macro to pull data from Multiple Worksheets into a Summary Worksheet

[satyavati0120]

Hi,

I usually receive automated excel report files which have Tabs Router tabs which contain bandwidth used and bandwidth free data.
(The sheet number varies daily)

Also, these automated excel reports have one slight glitch, they populate data in Column A & B, but the rows sometimes vary.

I am trying to create a VBA marco, so that once i run the macro, a summary sheet will be created and have all the router info tabulated and a bar chart showing bandwidth data generated in all Router worksheets. Snapshot pasted below.


I have attached the excel file for reference.
Let me know if anyone can help/advise?

Regards,
Satyajit

 

[AlanSidman]

Using Power Query, I brought your data into the PQ Editor and mashed up the data with the following Mcode and then close and loaded the data to a Pivot Chart. Be sure to change the source to your file name and location.

let
    Source = Excel.Workbook(File.Contents("C:\Users\alans\Downloads\BW_Summary Page.xlsm"), null, true),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Name", "Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Router Name] = "B/W Free" or [Router Name] = "B/W Used" or [Router Name] = "Description") and ([Bandwidth Used] <> "Percentage")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Bandwidth Free"}),
    #"Added Conditional Column" = Table.AddColumn(#"Removed Columns", "Free BW", each if Text.Contains([Router Name], "Free") then [Bandwidth Used] else "Null"),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Free BW", Int64.Type}}),
    #"Filled Up" = Table.FillUp(#"Changed Type",{"Free BW"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Up", each ([Router Name] = "B/W Used")),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"Router Name"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{"Bandwidth Used", Int64.Type}})
in
    #"Changed Type1"

 

[satyavati0120]

Thanks Alan

 

[satyavati0120]

@AlanSidman. I was trying to modify the Power Query to pull data from another type of Workbook which is very similar to the bandwidth sheet i shared for the previous example.

I have attached both the sheet from which I am trying to Pull the Data (VA_Sheet_Kaizen_TestSheet (version 7)) & a modified version of your Power Query Code (PQ Pivot Chart_For_VARatings)

In the PQ Pivot Chart sheet i am trying to pull in the Total VA Used & Free VA Available data.

For some reason i am not able to modify the query for the new excel sheet.

Can you please advise?

Season's Greetings,
Satyajit

 

[AlanSidman]

You have not explained what you want your pivot chart to look like. All you have said is you want to modify it. Suggest you explain clearly and demo with a mocked up pivot chart because I really have no ide what you are expecting. In the lead sheet, you indicate that #12 needs two transformers. How is that determined. What is the criteria?

 

[satyavati0120]

@AlanSidman
Apologies for the incomplete description.

I will try and explain it a bit better below:

Lead sheet:
1. The lead sheet generates Transformers depending on the component values.
2. The number of transformer tabs generated varies based on the component count and VA rating. (In the lead sheet example I shared, the sheet generated 2 Transformers)
3. Each Transformer worksheet will have the 'Total VA Used' & 'Free VA Available' data


Pivot Chart sheet:
1. The pivot sheet should generate the following summary sheet (based on the 2 transformer example in Lead sheet)
expected result for the Pivot sheet is pasted below:


Summary:
1. This looked very similar to the PQ editor you had created for the Router Bandwidth example (which works perfectly for Router sheets), so was trying to change the Router PQ chart query to pull in the Transformer Data.
2. Hopefully this provides more context.


Regards,
Satyajit