Help with code errors please

K

Kpm51

New member
Joined
Jul 2, 2013
Messages
11
Reaction score
0
Points
0
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
 
Hi Folks I thought I might reword my question in the hope of a response... I will post this new code which works very well if it is in the active workbook. How ever I want this code to run from my OfficeMacros workbook and apply it the process to the clients workbook. Is it possible to collect the ActiveWorkbook.name info of this line before it is asked for so that it can be gathered from somewhere else in the code?

Code: 
.Range("A2:AZ2").FormulaR1C1 = "='[" & ActiveWorkbook.Name & "]DATA STORAGE'!R3C"

This is the whole code for updating my sales worksheet from the information that was manually updated in my clients workbook. It comes to a halt at the above line. However if you ignor this error and run the macro again it works with an error 9 msg box prompt..

Code: 
Sub UpdateSalesSummary()

   Dim wbMaster 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\goflexhome\SALES 2013-14.xlsm", UpdateLinks:=False)
    End If
    
    With wbMaster.Sheets("CLIENT RECORDS")
    
    ActiveSheet.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

    ActiveWorkbook.Close savechanges:=True


End Sub
Any ideas would be appreciated. Thank you kindly
Kpm51 (Kirk)
 
Kirk, I'm just curious, does it work if you hard code the workbook name, rather than use ActiveWorkbook.Name?

Greg
 
Hi Greg, I resolved this issue. I thought it needed hard wiring also but it had to be updated with each new book I opened and was going to be working with. I was hoping some one could show me how to do it. Here is what has worked for me
Code: 
Sub UpdateAccountsSummary()
   Dim wbMaster As Workbook, WbSource As Workbook
   
   Dim cwBook As String
   
   On Error Resume Next
        cwBook = ActiveWorkbook.Name
        Set WbSource = Application.Workbooks(cwBook)
       
    On Error Resume Next
        Set wbMaster = Workbooks("ACCOUNTS 2013-14.xlsm")
    On Error GoTo 0
    If wbMaster Is Nothing Then
        Set wbMaster = Workbooks.Open(Filename:=" \\ Kezcomp\goflexhome/ACCOUNTS 2013-14.xlsm", UpdateLinks:=False)
    End If
    
    With wbMaster.Sheets("CLIENT RECORDS")
    
         ActiveSheet.Unprotect
         
        .Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
        .Range("A2:AZ2").FormulaR1C1 = "='[" & cwBook & "]DATA STORAGE'!R3C"
            ' Copy-Paste formats only
         WbSource.Sheets("Data Storage").Range("A3:AZ3").Copy
         .Range("A2").PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, False, False
         Application.CutCopyMode = True
   
    End With
    
    wbMaster.Save
    WbSource.Close savechanges:=True

End Sub
Thank you for your input its appreciated. Cheers Kirk P.S. My enter button doesn't move me to a new line when posting is there a trick to this?
 
My initial thought is that instead of referencing your formula directly, you could try just using the text value of the formula.
 
Not sure what you mean by that Greg but with the updated code the info that is required is sourced before the script runs so that all the relevant info is where it needs it. The macro is supposed to work on many unique or different client work books as their jobs are completed. If that makes sense.
Cheers
Kirk
 
You must log in or register to reply here.
Back
Top