Hi Folks,
I am using the following code in a workbook which when activated does the following:
Is there a way of performing this action with the workbook open.
I did think about placing a duplicate copy of the worksheet "Shift Manager Screen" in an archive workbook which will always be closed. Then get the workbook "Shift Manager.xls" to update from this archive folder by refreshing itself every so often. But i am not sure if that will work and how to do it. Also the information that it will need to update will mean that it will have to add up 1 row at a time for all new rows enter.
Can anyone help me to overcome this problem with a solution.
Thanks
I am using the following code in a workbook which when activated does the following:
- Opens the workbook "Shift Manager.xls" located in various folders in my G:Drive.
Locates the worksheet "Shift Manager Screen" in the workbook
Then performs the code which is in the with statement of the code below
Then saves and closes the workbook "Shift Manager.xls"
Is there a way of performing this action with the workbook open.
I did think about placing a duplicate copy of the worksheet "Shift Manager Screen" in an archive workbook which will always be closed. Then get the workbook "Shift Manager.xls" to update from this archive folder by refreshing itself every so often. But i am not sure if that will work and how to do it. Also the information that it will need to update will mean that it will have to add up 1 row at a time for all new rows enter.
Can anyone help me to overcome this problem with a solution.
Code:
Application.ScreenUpdating = False
Dim SMS As Worksheet
Workbooks.Open Filename:="G:\Cwmbran-new\Warehouse\lean manu\Mike C\HandPack Time Sheet\On LIne\" & _
"Shift Manager.xls" 'This opens the other workbook
Set SMS = Worksheets("Shift Manager Screen") 'This refers to worksheet in other workbook
With SMS
.Range("G8").Value = Val(.Range("G8").Value) + 1 & " Jobs Completed" 'Running Total Of Total Jobs Completed
.Range("D8").Value = Val(.Range("D8").Value) + Val(Me.TotalDiscsTextBox.Value) & " Total Discs Packed" 'Running Total Of Total Discs Packed
End With
ActiveWorkbook.Save 'Saves changes in the other workbook
ActiveWorkbook.Close 'Closes the other workbook
Application.ScreenUpdating = True
Thanks