Hi folks the code below works a treat after it errors twice. It errors after opening sales workbook ..and a restart creates an error in the second part of the script when opening the accounts workbook. Once we have been through these two erros and both books remain open it works like a charm for all new workbooks that I open and add two the various two books. I have a macro button on my quick access bar toolbar. My goal is to have it work first go and from 3 different computers. I can save the below code in a workbook called OfficeMacros or the personalworkbook to be shared depending on which one would best suit what I am trying to achieve for my project. My goal is tlo have this code work on execution and be available on the ribbon for all our computers to use. Any suggestions would be most appreciated. Cheers Kirk Here is my code....
Code:
Sub Sub UpdateClientBook()
Dim wbMaster As Workbook, wbMaster2 As Workbook
On Error Resume Next
Set wbMaster = Workbooks("SALES 2013-14.xlsm")
On Error GoTo 0
If wbMaster Is Nothing Then
Set wbMaster = Workbooks.Open(Filename:="\\KezComp\ SALES 2013-14.xlsm", UpdateLinks:=False)
End If
With wbMaster.Sheets("CLIENT RECORDS")
Sheet1.Unprotect
.Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
.Range("A2:AZ2").FormulaR1C1 = "='[" & ActiveWorkbook.Name & "]DATA STORAGE'!R3C"
' Copy-Paste formats only
ActiveWorkbook.Sheets("Data Storage").Range("A3:AZ3").Copy
.Range("A2").PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, False, False
Application.CutCopyMode = True
End With
wbMaster.Save
On Error Resume Next
Set wbMaster2 = Workbooks("ACCOUNTS 2013-14.xlsm")
On Error GoTo 0
If wbMaster2 Is Nothing Then
Set wbMaster2 = Workbooks.Open(Filename:="\\KezComp\ ACCOUNTS 2013-14.xlsm", UpdateLinks:=False)
End If
With wbMaster2.Sheets("CLIENT RECORDS")
Sheet1.Unprotect
.Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
.Range("A2:AZ2").FormulaR1C1 = "='[" & ActiveWorkbook.Name & "]DATA STORAGE'!R3C"
' Copy-Paste formats only
ActiveWorkbook.Sheets("Data Storage").Range("A3:AZ3").Copy
.Range("A2").PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, False, False
Application.CutCopyMode = True
End With
wbMaster2.Save
ActiveWorkbook.Close savechanges:=True
End Sub