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

K

Kpm51

New member
Joined
Jul 2, 2013
Messages
11
Reaction score
0
Points
0
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
         
[COLOR=#B22222]'This bit doesn't work and I don't know why'

                            .Range("K3").Select[/COLOR]
[COLOR=#B22222]       Selection.AutoFill Destination:=.Range("K2:K3"), Type:=xlFillDefault[/COLOR]
[COLOR=#B22222]               .Range("K2:K3").Select[/COLOR]

[COLOR=#B22222]                .Range("Z3").Select[/COLOR]
[COLOR=#B22222]       Selection.AutoFill Destination:=.Range("Z2:Z3"), Type:=xlFillDefault[/COLOR]
[COLOR=#B22222]                  .Range("Z2:Z3").Select[/COLOR]
   
    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:
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
 
You must log in or register to reply here.
Back
Top