Results 1 to 7 of 7

Thread: Help with code errors please

  1. #1

    Help with code errors please



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    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)

  3. #3
    Acolyte gsnidow's Avatar
    Join Date
    Aug 2011
    Location
    Virginia
    Posts
    38
    Articles
    0
    Kirk, I'm just curious, does it work if you hard code the workbook name, rather than use ActiveWorkbook.Name?

    Greg

  4. #4
    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?

  5. #5
    Acolyte gsnidow's Avatar
    Join Date
    Aug 2011
    Location
    Virginia
    Posts
    38
    Articles
    0
    My initial thought is that instead of referencing your formula directly, you could try just using the text value of the formula.

  6. #6
    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

  7. #7
    It seems a good idea

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •