Scheduling Sheet

[Valthanos]

Anyone able to provide some quick help? I have a worksheet I am working with to schedule fabrication. I need two macros to format the sheet and a background process.

Macro 1: Button Labeled "Fab Start";
sorts by Column I "Planned Start" oldest to newest
creates Week tags in Column A, adds double border above and below the week grouping. The Week tag should default to the Monday of the week from Planned Start Date.

Macro 1: Button Labeled "Req'd Complete";
sorts by Column O "Required Completion" oldest to newest
creates Week tags in Column A, adds double border above and below the week grouping. The Week tag should default to the Monday of the week from Required Completion date.

Background macro,
Insure that all dates are linear in each row from Material ship --> Planned Start --> Required Completion --> Install Start. Material ship date is optional, but if entered must validate. In the case that these dates are not linear a message box should appear identifying the issue with a option to enter updated dates into the message box which publish when the box is closed by clicking a button.

Here is my Excel File.

 

[VBE313]

Anyone able to provide some quick help? I have a worksheet I am working with to schedule fabrication. I need two macros to format the sheet and a background process.

Macro 1: Button Labeled "Fab Start";
sorts by Column I "Planned Start" oldest to newest
creates Week tags in Column A, adds double border above and below the week grouping. The Week tag should default to the Monday of the week from Planned Start Date.

Macro 1: Button Labeled "Req'd Complete";
sorts by Column O "Required Completion" oldest to newest
creates Week tags in Column A, adds double border above and below the week grouping. The Week tag should default to the Monday of the week from Required Completion date.

Background macro,
Insure that all dates are linear in each row from Material ship --> Planned Start --> Required Completion --> Install Start. Material ship date is optional, but if entered must validate. In the case that these dates are not linear a message box should appear identifying the issue with a option to enter updated dates into the message box which publish when the box is closed by clicking a button.

Here is my Excel File.

your file seems to be blank. I work in a fabrication & machining shop and just created a production schedule for the machining side. Would love to assist.

 

[Valthanos]

try this one

 

[Valthanos]

your file seems to be blank. I work in a fabrication & machining shop and just created a production schedule for the machining side. Would love to assist.

Can you show me what you developed? perhaps it can give me some ideas.

 

[VBE313]

Hi, your sheet seems to be blank again.

 

[Fluff]

Did you got to cell A1?

 

[Valthanos]

Hi, your sheet seems to be blank again.

Are you scrolling to the upper left? Not sure why but O365 opens it in the middle of the sheet.

 

[VBE313]

Got it,

What do you mean by
"creates Week tags in Column A, adds double border above and below the week grouping. The Week tag should default to the Monday of the week from Required "

 

[VBE313]

Paste these two codes in a module in your workbook.
Go to developer tab -> controls group -> Insert -> Form Controls Button. Right click and assign the macro StartFab to it. Do the same thing with Required Complete, once you specify what you want more for the required complete, I will update it. Also, Can you just do conditional formatting to make sure "Planned Start", "Required Complete", and "Install Start" are all filled out if "Material Ships" is filled in?

Sub StartFab()
    With Sheets("Sheet1").ListObjects("Table2")
        .Sort.SortFields.Clear
        .Sort.SortFields.Add2 _
        Key:=Range("Table2[[#Headers],[#Data],[Planned Start]]"), SortOn:= _
        xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    End With
    With Sheets("Sheet1").ListObjects("Table2").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
Sub reqComplete()
    With Sheets("Sheet1").ListObjects("Table2")
        .Sort.SortFields.Clear
        .Sort.SortFields.Add2 _
        Key:=Range("Table2[[#Headers],[#Data],[Required Completion]]"), SortOn:= _
        xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    End With
    With Sheets("Sheet1").ListObjects("Table2").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With