Error with Macro

[ian0886]

Hi Guys,

I have my macro below. On the line that is bolded and underline, I'm faced with a error message " Object variable or with block variable not set". I can't really figure what's missing in the variables that i've set. Appreciate the help!

Sub newspread()


    Dim wbMO2 As Workbook
    Set wbMO2 = Workbooks("family.xlsm") 'rem to change to trade tickets
    Dim trg As Worksheet, wsMO2(1 To 4) As Worksheet, colCount As Integer
     
    Set wsMO2(1) = wbMO2.Sheets("BP")
    Set wsMO2(2) = wbMO2.Sheets("TKY")
    Set wsMO2(3) = wbMO2.Sheets("family")


     
    Set trg = wbMO2.Worksheets.Add(After:=wbMO2.Worksheets(wbMO2.Worksheets.Count))
    trg.Name = "Master"
    
    Dim rng(1 To 2) As Range
    Set rng(1) = trg.UsedRange.Find("BNP", , xlValues, xlPart)
    
    For counter = 1 To 2
    With wsMO2(counter)
    .AutoFilterMode = False
    .Range("A2:I60").Copy
    End With
    
    With trg
        .Activate
        .Range("A" & Rows.Count).End(xlUp).PasteSpecial xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
[U][B][I]        .Range("A1").CurrentRegion.AutoFilter Field:=rng(1).Column, Criteria1:=""[/I][/B][/U]
        .Range("A1").CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilterMode = False
    End With
    Next counter
    
    trg.Range("a1").CurrentRegion.Copy
    
    With wsMO2(3)
        .Range("A2").Insert Shift:=xlDown
        .Columns.AutoFit
        .Columns("F:F").NumberFormat = "0.0000000"
        .Columns("B:B").NumberFormat = "d/mm/yyyy"
        .Columns("I:I").Value = .Columns("I:I").Value
    End With
End Sub

 

[Fluff]

Do you have any event code?
Because as Norie said, the code you have supplied is not pasting any data into the trg sheet

 

[ian0886]

Hi Norie,

I'm abit lost right now. What i'm trying to achieve is to copy from one sheet to trg. Afterwhich, i want to filter the header BNP and search for all the blanks and delete the blanks.

Subsequently, the next sheet is copied and pasted and the process is repeated. Could you advise how can i amend the macro to achieve this?