Help to update data repeatedly by VBA

[VISHAL120]

Hi All,

I don't know if this is really possible but still I would like to ask as this takes a lot of time for me to do everyday . Well I have one workbook which is the wip control where everyday data is being input for the qty process by the production dept. The file is then send to another dept which takes all the data from different dept to make a production progress

And everyday we have to take the data from the wip control, create a production progress by month if it does not exist, rename the sheet as per the week ending and put on the date updated manually and if the progress exist we just need to update the data on the progress daily.

I want to program this in vba where:

that is the production progress is kept on a folder, it scan the folder and if the progress do not exist it create it as per the month on the wip control;
rename the sheet as the week ending; and update the progress; and save.
Also in one month we will have different week ending so every time the week ending changes on the wip control it shall create and rename another sheet as the week ending and continue to update as previously

I have tried some of the codes which I have come around but it just saving the progress as per the month from the cell value and every time I am running the code it saving on a new one not updating the first one created.

I know this sound difficult but I would be very grateful if I can get a tips of how I can proceed as this takes almost everyday 2 - 3 hours work to update progress for all dept from their wip and which later we shall make further analysis.
please find attached the 2 workbook of how it is actually.

[Bob Phillips]

Option Explicit
Const PROGRESS_FOLDER As String = "C:\test\" '<--- change to suit
Const PROGRESS_BASE As String = "PROD_PROGRESS_<month>.xls" '<--- change to suit

Public Sub UpdateWIPData()
Dim wbProgress As Workbook
Dim shProgress As Worksheet
With Worksheets("CONTROL")
Set wbProgress = ProgressWB(.Range("H2").Text, .Range("F2").Value)
Set shProgress = wbProgress.Worksheets(wbProgress.Worksheets.Count)
.Cells.Copy shProgress.Range("A1")
End With
With shProgress
.Range("A1").Value = "PRODUCTION PROGRESS FOR MONTH"
.Range("A1").Font.Bold = True
.Range("C2").Value = "DATE UPDATE"
.Range("C2").HorizontalAlignment = xlRight
.Range("E2:F2").ClearContents
.Range("F2").BorderAround Weight:=xlThin, ColorIndex:=xlColorIndexNone
End With
wbProgress.Save
wbProgress.Close
End Sub

Private Function ProgressWB(MonthID As String, WEDate As Date)
Dim sh As Worksheet
Dim i As Long
On Error Resume Next
If Dir(PROGRESS_FOLDER & Replace(PROGRESS_BASE, "<month>", MonthID), vbNormal) <> "" Then
Set ProgressWB = Workbooks.Open(PROGRESS_FOLDER & Replace(PROGRESS_BASE, "<month>", MonthID))
With ProgressWB
Set sh = .Worksheets.Add(after:=.Worksheets(.Worksheets.Count))
sh.Name = "WK_" & Format(WEDate, "dd_mm")
End With
Else
Application.DisplayAlerts = False
Set ProgressWB = Workbooks.Add
For i = ProgressWB.Worksheets.Count To 2 Step -1
ProgressWB.Worksheets(i).Delete
Next I
ProgressWB.SaveAs Filename:=PROGRESS_FOLDER & Replace(PROGRESS_BASE, "<month>", MonthID)
ProgressWB.Worksheets(1).Name = "WK_" & Format(WEDate, "dd_mm")
Application.DisplayAlerts = True
End If
End Function

[GTO]

@XLD:

Hi Bob :-)

Maybe just me, but 2000 didn't seem to like xlColorIndexNone used in .BorderAround? Not sure why.

Towards the end of UpdateWIPData()


'// For me (currently in 2000), these all gave 'BorderAround method of Range class failed' //
'// Admittedly not well tested, but .BorderAround didn't seem to like xlColorIndexNone //
'// I am clueless as to why... //
On Error Resume Next
.Range("F2").BorderAround Weight:=xlThin, ColorIndex:=xlColorIndexNone
Debug.Print Err.Description
Err.Clear
.Range("F2").BorderAround LineStyle:=xlLineStyleNone, ColorIndex:=xlColorIndexNone
Debug.Print Err.Description
Err.Clear
.Range("F2").BorderAround ColorIndex:=xlColorIndexNone
Debug.Print Err.Description
On Error GoTo 0
'// These both seemed to work though.
.Range("F2").Borders.ColorIndex = xlColorIndexNone
.Range("F2").Borders.LineStyle = xlLineStyleNone
End With

Apologies if I am missing something.

A great day to all,

Mark

[VISHAL120]

Hi Xld,

Many thanks for your precious time . I have place your code on the workbook and here is the outcome:

1st. I have a runtime error msg like this :" BorderAround method of Range class failed.

2Nd. it not saving on the test folder which is the default folder to save the progress. i think

3RD: it not saving as the month that is like prod_progres_Aug but instead it saving as testprod_progress_month. normaly it shall save by the month according to the main wip that is if the month ( this will be done by vlookup on the main) on the main is AUG, Sept it shall save the progress by that month that is from the range("H2") from the main and for the sheet it shall take the week ending from range("F2").

4TH: When the week ending is same and when the date is change on the Main it do not save the data on the same week ending but instead it save it on a new sheet. normally a week ending is define as start from Monday to Sunday for example week ending 28-08 will be from 22-08 to 28-08 and the data shall be save on the same week ending because at the end of the week we shall see what has been produce for that week ending. Because the progress take all the production from Monday to sunday to make a cummulative for the week ending which later is used to make for the month. That is why it shall save 1st by Month, then by Week ending.
i don't if i have been able to explained it correctly.

5TH: when i change it week ending it save as per the week defined this one is working ok.

thanks to revert if this is possible.

Many thanks again for the help now i have the hope that we can save a lot of time through this.

[VISHAL120]

Hi Xld,

Many thanks for your precious time . I have place your code on the workbook and here is the outcome:

1st. I have a runtime error msg like this :" BorderAround method of Range class failed.

2Nd. it not saving on the test folder which is the default folder to save the progress. i think

3RD: it not saving as the month that is like prod_progres_Aug but instead it saving as testprod_progress_month. normaly it shall save by the month according to the main wip that is if the month ( this will be done by vlookup on the main) on the main is AUG, Sept it shall save the progress by that month that is from the range("H2") from the main and for the sheet it shall take the week ending from range("F2").

4TH: When the week ending is same and when the date is change on the Main it do not save the data on the same week ending but instead it save it on a new sheet. normally a week ending is define as start from Monday to Sunday for example week ending 28-08 will be from 22-08 to 28-08 and the data shall be save on the same week ending because at the end of the week we shall see what has been produce for that week ending. Because the progress take all the production from Monday to sunday to make a cummulative for the week ending which later is used to make for the month. That is why it shall save 1st by Month, then by Week ending.
i don't if i have been able to explained it correctly.

5TH: when i change it week ending it save as per the week defined this one is working ok.

I have attached the 2 files with code for example. thanks to have a look.

thanks to revert if this is possible.

Many thanks again for the help now i have the hope that we can save a lot of time through this.

[VISHAL120]

Hi ,

Please can anyone help or just give a hint if this is possible to do.

I will be much grateful