VBA Code

[ongengthai1973]

I have 2 files.

File A
Master templates dates

File B
Scheduling file.
My question : I would like to use VBA code when I key in date (3/10/22) in columns M and VBA code auto fill up the dates start from L3 to B3 (Backward scheduling) based on File A.

 

[Zyndstoff]

Will the Master Template Dates in File A change? Or is it a fixed table?
Will the template dates always be in Row 3?

 

[Zyndstoff]

Coding to enter in File B (the two "File A" must be changed to pathname\filename of master file A):

Private Sub Worksheet_Change(ByVal Target As Range)
 
  On Error Resume Next
  Workbooks("File A").close savechanges:=false 'close the Masterfile, if opened
  On Error goto 0

  Dim Master As Workbook
  Dim Schedule As Worksheet
  Dim R, i, Delta As Integer
 
  If Target.Column <> 13 Then Exit Sub 'Only react to changes in column M
  If IsDate(Target) = False Then
    MsgBox "No Date"
    Exit Sub
  End If
 
  R = Target.Row 'Set variable R to the correct row
  Set Schedule = ActiveSheet 'Set Schedule File B Worksheet as Schedule
  Set Master = Application.Workbooks.Open("File A") Open Master File A and set as "Master"
 
  For i = 12 To 2 Step -1
    Delta = DateDiff("d", Master.Worksheets(1).Cells(3, i + 1), Master.Worksheets(1).Cells(3, i))
    Schedule.Cells(R, i) = DateAdd("d", Delta, Schedule.Cells(R, i + 1))
  Next i
 
  Master.Close savechanges:=False 'Close the Master File A
 
  Set Master = Nothing
  Set Schedule = Nothing
 
End Sub

 

[ongengthai1973]

Sir,
Thank you so much.VBA code able to run smoothly.

 

[Zyndstoff]

You're welcome.