VBA Code Help

markster

Well-known Member
Joined
May 23, 2002
Messages
568
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I've got a massive scheduling job to do from a spreadsheet with about 1300 lines and could really do with some VBA code to help me.

There are three sheets:

SHEET 1 - Details of working patterns - data is contained in columns A to W but I've hidden some columns so the screen shot isn't too big

SHEET 2 - Individual Weeks, Dates and Days of the week

SHEET 3 - The summary I need to be created by the Macro from Sheets 1 & 2

In order to create the summary I need in SHEET 3, it needs to look at 2 key columns in SHEET 1 (Column G - Week Range & Column Q Day Range) and Columns - A-F in SHEET 2. In the summary, it splits out out the week numbers into separate lines with actual dates and includes all data from SHEET 1 (I mean entire row of data A to W). I could try to explain it more in words but I think looking at the SHEET 3 screenshot illustrates what I need and trying to explain further might cause confusion. Obviously, I'm happy to answer any questions you may have.

SHEET 1 - Details of working patterns - data is contained in columns A to W but I've hidden some columns so the screen shot isn't too big

1629199490576.png


Sheet 2 - Individual Weeks, Dates and Days of the week

1629199545227.png


Sheet 3 - The summary I need to be created by the Macro from Sheets 1 & 2 (Desired Output)

1629199565001.png


Thanks
Mark
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
I'm certainly not an expert in the M-language. There a probably more elegant solutions. But I'm still learning. :) Just there are too many things to do and learn.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

markster

Well-known Member
Joined
May 23, 2002
Messages
568
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
hi @bobsan42 - hope that you are well. In post number 5 above you say

Then you need to create 3 Data sets and load them as Connections only using PowerQuery:

I've been trying to figure out how to do this but can't - can you talk me through the steps? Much appreciated Mark
 

markster

Well-known Member
Joined
May 23, 2002
Messages
568
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hi @bobsan42 or anyone else who can help . This urgent scheduling job got put on one side as I had to rescue a different project but now it's become urgent again. I want to use your solution that's described in post #5. I created my 3 tables exactly as you specified. Then you say "create 3 Data sets and load them as Connections only using PowerQuery". I've been trying to figure out how to do this bit, but there's no youtube videos that I can find that might help and couldn't find any help on the web so I was wondering if you could describe the steps I need to do for this bit. Thanks in advance - Mark
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Sorry, i cannot make a guide for you since I am not with a computer these days.
I am surprised you cannot find it online.
I will improvise on my mobile.
Select a table. Go to data tab / get and transform from table.
This shall take you to the power query editor.
Under Close and load select Load To ...
In the next window select Connection Only.

However, in post #9 i suggested a possible solution with all-in-one query. It transforms the data without the need for others. Plus it takes into account the new caveats from post #8. Can't you implement it?
 

markster

Well-known Member
Joined
May 23, 2002
Messages
568
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Sorry, i cannot make a guide for you since I am not with a computer these days.
I am surprised you cannot find it online.
I will improvise on my mobile.
Select a table. Go to data tab / get and transform from table.
This shall take you to the power query editor.
Under Close and load select Load To ...
In the next window select Connection Only.

However, in post #9 i suggested a possible solution with all-in-one query. It transforms the data without the need for others. Plus it takes into account the new caveats from post #8. Can't you implement it?
ok thanks @bobsan42 in the post 9 do i put the individual workbooks that link in a single folder? Thanks again. Mark
 

markster

Well-known Member
Joined
May 23, 2002
Messages
568
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Also I was a bit confused about this instruction - Pick a cell in your workbook, name it nmStart (Scope: Workbook) and fill in the starting date of of the first week maybe 27-09-2021 . I put workbooks, 1, 2, 3 4 in the same folder?
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows

ADVERTISEMENT

Also I was a bit confused about this instruction - Pick a cell in your workbook, name it nmStart (Scope: Workbook) and fill in the starting date of of the first week maybe 27-09-2021 .
I don't understand what is confusing.
Instead of listing all dates and days in sheet2 you only need the first day date . The rest will be calculated.
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
I put workbooks, 1, 2, 3 4 in the same folder?
It doesn't matter. Wherever you place them you have to fill the correct paths in the code below on the lines for src1 to src4:
Power Query:
let
    wdays = Table.TransformColumnTypes(Table.FromRows({{"Monday",0},{"Tuesday",1},{"Wednesday",2},{"Thursday",3},{"Friday",4},{"Saturday",5},{"Sunday",6}},{"Day","Num"}),{{"Day", type text}, {"Num", Int64.Type}}),
    sn = Number.From(Excel.CurrentWorkbook(){[Name="nmStart"]}[Content]{0}[Column1]),

    src1 = Table.PromoteHeaders(Excel.Workbook(File.Contents("C:\Users\...\Desktop\Workbook1.xlsx"), null, true){[Item="Sheet1",Kind="Sheet"]}[Data], [PromoteAllScalars=true]),
    src2 = Table.PromoteHeaders(Excel.Workbook(File.Contents("C:\Users\...\Desktop\Workbook2.xlsx"), null, true){[Item="Sheet1",Kind="Sheet"]}[Data], [PromoteAllScalars=true]),
    src3 = Table.PromoteHeaders(Excel.Workbook(File.Contents("C:\Users\...\Desktop\Workbook3.xlsx"), null, true){[Item="Sheet1",Kind="Sheet"]}[Data], [PromoteAllScalars=true]),
    src4 = Table.PromoteHeaders(Excel.Workbook(File.Contents("C:\Users\...\Desktop\Workbook4.xlsx"), null, true){[Item="Sheet1",Kind="Sheet"]}[Data], [PromoteAllScalars=true]),
    AllSource = Table.Combine({src1,src2,src3,src4}),

    Source = Table.NestedJoin(AllSource, {"Day"}, wdays, {"Day"}, "tbWeekdays", JoinKind.LeftOuter),
    #"Expanded tbWeekdays" = Table.ExpandTableColumn(Source, "tbWeekdays", {"Num"}, {"Num"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(#"Expanded tbWeekdays", {{"WeekRange(s)", type text}}, "bg-BG"), {{"WeekRange(s)", Splitter.SplitTextByDelimiter(",", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "WeekRange(s)"),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"WeekRange(s)", type text}, {"Duration", type time}, {"Finish time", type time}, {"Start time", type time}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "WeekRange(s)", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"WR1", "WR2"}),
    typechange1= Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"WR1", Int64.Type}, {"WR2", Int64.Type}}),
    replace1 = Table.ReplaceValue(typechange1,null, each _[WR1],Replacer.ReplaceValue,{"WR2"}),
    listnums = Table.AddColumn(replace1,"CustomList",each List.Numbers([WR1]-1,[WR2]-[WR1]+1)),
    expandList = Table.ExpandListColumn(listnums, "CustomList"),
    #"Added Custom" = Table.AddColumn(expandList, "Custom", each [CustomList]*7+[Num]+sn),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Custom", "Date"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Col1", "Activity Code", "Column1", "Activity", "Column2", "Column3", "Date", "WR1", "WR2", "Column4", "Shift Worker", "Zone", "Column5", "Column6", "Column7", "Column8", "No in Room", "Room", "Day", "Start time", "Finish time", "Duration", "Column9", "Column10", "Reference", "Num", "CustomList"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"WR1", "WR2", "Num", "CustomList"})
in
    #"Removed Columns"
Discard the code from post#9 there is a mistake in it .
 

Forum statistics

Threads
1,147,497
Messages
5,741,499
Members
423,662
Latest member
Ajmal Khursand

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top