Object Required

[Mugalh01]

Hi all,

It could be that I've been at this for too long and I now can't see the obvious errors. I cannot get past the "object required" error - by the autofilter line- Any help will be greatly appreciated.

[I]Sub copysubpanel_NTC()[/I]

[I]Set FPNTC = Sheets("Full Panel NTC check")[/I]
[I]Set SPNTC = Sheets("Subpanel NTC check")[/I]

[I]Dim Lrow As Long[/I]

[I]With FPNTC[/I]
[I]Lrow = .Cells(Rows.Count, 1).End(xlUp).Row[/I]

[I].Range("H2:H234").FormulaR1C1 = "=IF('Patient demographics'!R2C6=""Colorectal"",ISNUMBER(MATCH(C[-6], 'PanCancer Panels'!C[-7],0)),IF('Patient demographics'!R2C6=""Breast"",ISNUMBER(MATCH(C[-6], 'PanCancer Panels'!C[-6],0)),IF('Patient demographics'!R2C6=""GIST"",ISNUMBER(MATCH(C[-6], 'PanCancer Panels'!C[-5],0)),IF('Patient demographics'!R2C6=""Glioma"",ISNUMBER(MATCH(C[-6], 'PanCancer Panels'!C[-4],0)),IF('Patient demographics'!R2C6=""HN"",ISNUMBER(MATCH(C[-6], 'PanCancer Panels'!C[-3],0)),IF('Patient demographics'!R2C6=""Lung"",ISNUMBER(MATCH(C[-6], 'PanCancer Panels'!C[-2],0)),IF('Patient demographics'!R2C6=""Melanoma"",ISNUMBER(MATCH(C[-6], 'PanCancer Panels'!C[-1],0)),IF('Patient demographics'!R2C6=""Ovarian"",ISNUMBER(MATCH(C[-6], 'PanCancer Panels'!C,0)),IF('Patient demographics'!R2C6=""Prostate"",ISNUMBER(MATCH(C[-6], 'PanCancer Panels'!C[1],0)),IF('Patient demographics'!R2C6=""Thyroid"",ISNUMBER(MATCH(C[-6], 'PanCancer Panels'!C[2],0)),FALSE))))))))))"[/I]

[B][I]FPNTC.Cells.Selection.AutoFilter.Range("A1").AutoFilter Field:=8, Criteria1:="TRUE"[/I][/B]
[I]FPNTC.Range("A2:H" & Lrow).Select[/I]
[I]Selection.Copy[/I]
[I]SPNTC.Range("A2").PasteSpecial xlPasteValues[/I]

[I]Application.CutCopyMode = False[/I]

[I]End With[/I]
[I]
[End Code]. 

Thank you[/I]
[I]End Sub[/I]

 

[NdNoviceHlp]

You have no selection before the filter. HTH. Dave

 

[Mugalh01]

Thanks Dave, I've tweaked the code as suggested and it seems to be working fine - the question I've got is if I'm within the 'With' function (i.e. 'with FPNTC' as in the code below), why do i still have to state the sheet further down (i.e. 'sheets ("Full Panel NTC check")? Just so that I can better understand VBA.

[/I][I]Sub copysubpanel_NTC()[/I]

[I]Set FPNTC = Sheets("Full Panel NTC check")[/I]
[I]Set SPNTC = Sheets("Subpanel NTC check")[/I]

[I]Dim Lrow As Long[/I]

[I]With FPNTC[/I]
[I]Lrow = .Cells(Rows.Count, 1).End(xlUp).Row[/I]

[I].Range("H2:H234").FormulaR1C1 = "=IF('Patient demographics'!R2C6=""Colorectal"",ISNUMBER(MATCH(C[-6], 'PanCancer Panels'!C[-7],0)),IF('Patient demographics'!R2C6=""Breast"",ISNUMBER(MATCH(C[-6], 'PanCancer Panels'!C[-6],0)),IF('Patient demographics'!R2C6=""GIST"",ISNUMBER(MATCH(C[-6], 'PanCancer Panels'!C[-5],0)),IF('Patient demographics'!R2C6=""Glioma"",ISNUMBER(MATCH(C[-6], 'PanCancer Panels'!C[-4],0)),IF('Patient demographics'!R2C6=""HN"",ISNUMBER(MATCH(C[-6], 'PanCancer Panels'!C[-3],0)),IF('Patient demographics'!R2C6=""Lung"",ISNUMBER(MATCH(C[-6], 'PanCancer Panels'!C[-2],0)),IF('Patient demographics'!R2C6=""Melanoma"",ISNUMBER(MATCH(C[-6], 'PanCancer Panels'!C[-1],0)),IF('Patient demographics'!R2C6=""Ovarian"",ISNUMBER(MATCH(C[-6], 'PanCancer Panels'!C,0)),IF('Patient demographics'!R2C6=""Prostate"",ISNUMBER(MATCH(C[-6], 'PanCancer Panels'!C[1],0)),IF('Patient demographics'!R2C6=""Thyroid"",ISNUMBER(MATCH(C[-6], 'PanCancer Panels'!C[2],0)),FALSE))))))))))"[/I]

[I]Sheets("Full Panel NTC check").Select[/I]
[I]Cells.Select[/I]
[I]Selection.AutoFilter[/I]
[I]Range("A1").AutoFilter Field:=8, Criteria1:="TRUE"[/I]
[I]FPNTC.Range("A2:H" & Lrow).Select[/I]
[I]Selection.Copy[/I]
[I]SPNTC.Range("A2").PasteSpecial xlPasteValues[/I]

[I]Application.CutCopyMode = False[/I]

[I]End With[/I]

[I]End Sub

[End CODE][/I]

 

[sandy666]

instead of this

...
[End CODE][/I]
[/QUOTE]
use
[PLAIN][CODE]

[/PLAIN]

 

[Fluff]

There's no need to select anything, try

Sub copysubpanel_NTC()
   Dim FPNTC As Worksheet, SPNTC As Worksheet

   Set FPNTC = Sheets("Full Panel NTC check")
   Set SPNTC = Sheets("Subpanel NTC check")
   
   Dim Lrow As Long
   
   With FPNTC
      Lrow = .Cells(Rows.Count, 1).End(xlUp).Row
      
      .Range("H2:H" & Lrow).FormulaR1C1 = "=IF('Patient demographics'!R2C6=""Colorectal"",ISNUMBER(MATCH(C[-6], 'PanCancer Panels'!C[-7],0)),IF('Patient demographics'!R2C6=""Breast"",ISNUMBER(MATCH(C[-6], 'PanCancer Panels'!C[-6],0)),IF('Patient demographics'!R2C6=""GIST"",ISNUMBER(MATCH(C[-6], 'PanCancer Panels'!C[-5],0)),IF('Patient demographics'!R2C6=""Glioma"",ISNUMBER(MATCH(C[-6], 'PanCancer Panels'!C[-4],0)),IF('Patient demographics'!R2C6=""HN"",ISNUMBER(MATCH(C[-6], 'PanCancer Panels'!C[-3],0)),IF('Patient demographics'!R2C6=""Lung"",ISNUMBER(MATCH(C[-6], 'PanCancer Panels'!C[-2],0)),IF('Patient demographics'!R2C6=""Melanoma"",ISNUMBER(MATCH(C[-6], 'PanCancer Panels'!C[-1],0)),IF('Patient demographics'!R2C6=""Ovarian"",ISNUMBER(MATCH(C[-6], 'PanCancer Panels'!C,0)),IF('Patient demographics'!R2C6=""Prostate"",ISNUMBER(MATCH(C[-6], 'PanCancer Panels'!C[1],0)),IF('Patient demographics'!R2C6=""Thyroid"",ISNUMBER(MATCH(C[-6], 'PanCancer Panels'!C[2],0)),FALSE))))))))))"
      
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("A1").AutoFilter Field:=8, Criteria1:="TRUE"
      .Range("A2:H" & Lrow).Copy
      SPNTC.Range("A2").PasteSpecial xlPasteValues
      
      Application.CutCopyMode = False
      .AutoFilterMode = False
   End With

End Sub

 

[Mugalh01]

My Bad Sandy,

still learning; will get there eventually

 

[Mugalh01]

Fluff I don't know what you've done here but it works beautifully!! Thank you.
I'll analyse it and use it elsewhere.
Thanks again.

 

[Fluff]

You're welcome & thanks for the feedback