Results 1 to 2 of 2

Thread: Manipulating worksheet data after data insert on worksheet Only half my code works

  1. #1

    Manipulating worksheet data after data insert on worksheet Only half my code works



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

    Hi Folks,
    I was wondering if someone could help me with a piece of code that works for the first part but when I add the second part to be processed on the same worksheet it wont execute the second part of the code.
    Here is the code part 1 (working code)

    Code:
       On Error GoTo 0
            cwBook = ActiveWorkbook.Name
            Set wbSource = Application.Workbooks(cwBook)
    
    
        On Error Resume Next
            Set wbBookings = Workbooks("CURRENT BOOKINGS.xlsm")
        On Error GoTo 0
        If wbBookings Is Nothing Then
            Set wbBookings = Workbooks.Open(fileName:="\\home\CURRENT BOOKINGS.xlsm", UpdateLinks:=False)
        End If
    
    
        With wbBookings.Sheets("CLIENT RECORDS")
    
    
        ActiveSheet.Unprotect
    
    
           .Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
            .Range("A2:P2").FormulaR1C1 = "='[" & cwBook & "]DATA STORAGE'!R3C"
                ' Copy-Paste formats only
                wbSource.Sheets("DATA STORAGE").Range("A29:P29").Copy
                .Range("A2").PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, False, False
                
               
     End With
     
        wbBookings.Save
    Here is the same code with the second half of my code included but I cant get it to work.
    Code:
             
        On Error Resume Next
            cwBook = ActiveWorkbook.Name
            Set wbSource = Application.Workbooks(cwBook)
    
    
           
        On Error Resume Next
            Set wbAccounts = Workbooks("CURRENT CLIENT ACCOUNTS SUMMARIES.xlsm")
        On Error GoTo 0
        If wbAccounts Is Nothing Then
            Set wbAccounts = Workbooks.Open(fileName:="\\home\kirk\CURRENT CLIENT ACCOUNTS SUMMARIES.xlsm", UpdateLinks:=False)
        End If
        
        With wbAccounts.Sheets("CLIENT RECORDS")
        
             ActiveSheet.Unprotect
             
            .Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
            .Range("A2:Z2").FormulaR1C1 = "='[" & cwBook & "]DATA STORAGE'!R3C"
                ' Copy-Paste formats only
             wbSource.Sheets("DATA STORAGE").Range("A53:Z53").Copy
             .Range("A2").PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, False, False
             Application.CutCopyMode = True
             
    'This bit doesn't work and I don't know why'
    
                                .Range("K3").Select
           Selection.AutoFill Destination:=.Range("K2:K3"), Type:=xlFillDefault
                   .Range("K2:K3").Select
    
                    .Range("Z3").Select
           Selection.AutoFill Destination:=.Range("Z2:Z3"), Type:=xlFillDefault
                      .Range("Z2:Z3").Select
       
        End With
    Any pointers or code that does do the above two proceedures in the one action would be appreciated.

    Thank you kindly
    Kirk
    Last edited by Kpm51; 2015-06-14 at 03:27 AM.

  2. #2
    I have resolved the above code with some further reseach and came up with this.
    I removed . select from the new lines
    Code:
       On Error GoTo 0
             cwBook = ActiveWorkbook.Name
            Set wbSource = Application.Workbooks(cwBook)
    
    
        On Error Resume Next
            Set wbBookings = Workbooks("CURRENT BOOKINGS.xlsm")
        On Error GoTo 0
        If wbBookings Is Nothing Then
            Set wbBookings = Workbooks.Open(filename:="\\BA-36C35E\kkretic\CURRENT BOOKINGS.xlsm", UpdateLinks:=False)
        End If
    
    
        With wbBookings.Sheets("CLIENT RECORDS")
    
    
        ActiveSheet.Unprotect
    
    
           .Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
            .Range("A2:P2").FormulaR1C1 = "='[" & cwBook & "]DATA STORAGE'!R3C"
                ' Copy-Paste formats only
                wbSource.Sheets("DATA STORAGE").Range("A29:P29").Copy
                .Range("A2").PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, False, False
                
                    .Range("K3").AutoFill Destination:=.Range("K2:K3"), Type:=xlFillDefault
    '                .Range ("K2:K3")
                
                    .Range("P3").AutoFill Destination:=.Range("P2:P3"), Type:=xlFillDefault
    '                .Range ("Z2:Z3") accounts
    
    
    
    
    End With
    but now I am trying to add filter for the worksheet cell vale J2 to the above code.
    Any ideas thanks

    Kirk

Posting Permissions

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