Autofilter, copy & paste vba not working

eviehc123

New Member
Joined
Jan 21, 2019
Messages
30
Hi can someone have a look at the below and help with why it's not working?

Warning it's messy!

Code:
Sub Searchproper()
'
' copyandpaste Macro
'


'
    Sheets("HR Advice & Admin").Select
    FilterString = Sheets("Offer Received").Range("G5").Value
    ActiveSheet.Range("$A$1:$AS$286").AutoFilter Field:=1, Criteria1:=FilterString
    Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
    Intersect(.AutoFilter.Range.Offset(1), .Range("B:B")).Copy
    Sheets("Offer Received").Range("B5").PasteSpecial xlPasteValues
    Intersect(.AutoFilter.Range.Offset(1), .Range("C:c")).Copy
    Sheets("Offer Received").Range("B10").PasteSpecial xlPasteValues
    Range("D2:D" & Cells(Rows.Count, "D").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
    Intersect(.AutoFilter.Range.Offset(1), .Range("D:d")).Copy
    Sheets("Offer Received").Range("B12").PasteSpecial xlPasteValues
    Range("E2:E" & Cells(Rows.Count, "E").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
    Intersect(.AutoFilter.Range.Offset(1), .Range("E:e")).Copy
    Sheets("Offer Received").Range("B14").PasteSpecial xlPasteValues
    Range("F2:F" & Cells(Rows.Count, "F").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
    Intersect(.AutoFilter.Range.Offset(1), .Range("F:f")).Copy
    Sheets("Offer Received").Range("B16").PasteSpecial xlPasteValues
    Intersect(.AutoFilter.Range.Offset(1), .Range("G:g")).Copy
    Sheets("Offer Received").Range("E8").PasteSpecial xlPasteValues
    Range("H2:H" & Cells(Rows.Count, "H").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
    Intersect(.AutoFilter.Range.Offset(1), .Range("H:h")).Copy
    Sheets("Offer Received").Range("E10").PasteSpecial xlPasteValues
    Range("I2:I" & Cells(Rows.Count, "I").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
    Intersect(.AutoFilter.Range.Offset(1), .Range("I:i")).Copy
    Sheets("Offer Received").Range("E12").PasteSpecial xlPasteValues
    Range("J2:J" & Cells(Rows.Count, "J").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
    Intersect(.AutoFilter.Range.Offset(1), .Range("J:j")).Copy
    Sheets("Offer Received").Range("E14").PasteSpecial xlPasteValues
    Range("K2:K" & Cells(Rows.Count, "K").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
    Intersect(.AutoFilter.Range.Offset(1), .Range("K:k")).Copy
    Sheets("Offer Received").Range("E18").PasteSpecial xlPasteValues
    Range("L2:L" & Cells(Rows.Count, "L").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
    Intersect(.AutoFilter.Range.Offset(1), .Range("L:l")).Copy
    Sheets("Offer Received").Range("E20").PasteSpecial xlPasteValues
    Range("M2:M" & Cells(Rows.Count, "M").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
    Intersect(.AutoFilter.Range.Offset(1), .Range("M:m")).Copy
    Sheets("Offer Received").Range("H8").PasteSpecial xlPasteValues
    Range("N2:N" & Cells(Rows.Count, "N").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
    Intersect(.AutoFilter.Range.Offset(1), .Range("N:n")).Copy
    Sheets("Offer Received").Range("H10").PasteSpecial xlPasteValues
    Range("P2:P" & Cells(Rows.Count, "P").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
    Intersect(.AutoFilter.Range.Offset(1), .Range("P:p")).Copy
    Sheets("Offer Received").Range("H14").PasteSpecial xlPasteValues
    Range("Q2:Q" & Cells(Rows.Count, "Q").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
    Intersect(.AutoFilter.Range.Offset(1), .Range("Q:q")).Copy
    Sheets("Offer Received").Range("H20").PasteSpecial xlPasteValues
    Range("R2:R" & Cells(Rows.Count, "R").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
    Intersect(.AutoFilter.Range.Offset(1), .Range("R:r")).Copy
    Sheets("Offer Received").Range("B23").PasteSpecial xlPasteValues
    Range("S2:S" & Cells(Rows.Count, "S").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
    Intersect(.AutoFilter.Range.Offset(1), .Range("S:s")).Copy
    Sheets("Offer Received").Range("B25").PasteSpecial xlPasteValues
    Range("T2:T" & Cells(Rows.Count, "T").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
    Intersect(.AutoFilter.Range.Offset(1), .Range("T:t")).Copy
    Sheets("Offer Received").Range("B27").PasteSpecial xlPasteValues
    Range("U2:U" & Cells(Rows.Count, "U").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
    Intersect(.AutoFilter.Range.Offset(1), .Range("U:u")).Copy
    Sheets("Offer Received").Range("B29").PasteSpecial xlPasteValues
    Range("V2:V" & Cells(Rows.Count, "V").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
    Intersect(.AutoFilter.Range.Offset(1), .Range("V:v")).Copy
    Sheets("Offer Received").Range("E23").PasteSpecial xlPasteValues
    Range("W2:W" & Cells(Rows.Count, "W").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
    Intersect(.AutoFilter.Range.Offset(1), .Range("W:w")).Copy
    Sheets("Offer Received").Range("E25").PasteSpecial xlPasteValues
    Range("X2:X" & Cells(Rows.Count, "X").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
    Intersect(.AutoFilter.Range.Offset(1), .Range("X:x")).Copy
    Sheets("Offer Received").Range("E27").PasteSpecial xlPasteValues
    Range("Y2:Y" & Cells(Rows.Count, "Y").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
    Intersect(.AutoFilter.Range.Offset(1), .Range("Y:y")).Copy
    Sheets("Offer Received").Range("E29").PasteSpecial xlPasteValues
    Sheets("HR Advice & Admin").Select
    Selection.AutoFilter
    Sheets("Offer Received").Select
    Application.CutCopyMode = False
End Sub
Thanks!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,440
Office Version
365
Platform
Windows
It's not working because you have changed the code I suggested.
Try changing it back the way I had it & see if that works.
 
Last edited:

eviehc123

New Member
Joined
Jan 21, 2019
Messages
30
It's not working because you have changed the code I suggested.
Try changing it back the way I had it & see if that works.
Oh yeah sorry!

Please see below. Think it's the same now but still not working?

Code:
Sub Searchproper()
'
' copyandpaste Macro
'


'
    Sheets("HR Advice & Admin").Select
    FilterString = Sheets("Offer Received").Range("G5").Value
    .Range("$A$1:$AS$286").AutoFilter Field:=1, Criteria1:=FilterString
    Intersect(.AutoFilter.Range.Offset(1), .Range("B:B")).Copy
    Sheets("Offer Received").Range("B5").PasteSpecial xlPasteValues
    Intersect(.AutoFilter.Range.Offset(1), .Range("C:c")).Copy
    Sheets("Offer Received").Range("B10").PasteSpecial xlPasteValues
    Intersect(.AutoFilter.Range.Offset(1), .Range("D:d")).Copy
    Sheets("Offer Received").Range("B12").PasteSpecial xlPasteValues
    Intersect(.AutoFilter.Range.Offset(1), .Range("E:e")).Copy
    Sheets("Offer Received").Range("B14").PasteSpecial xlPasteValues
    Intersect(.AutoFilter.Range.Offset(1), .Range("F:f")).Copy
    Sheets("Offer Received").Range("B16").PasteSpecial xlPasteValues
    Intersect(.AutoFilter.Range.Offset(1), .Range("G:g")).Copy
    Sheets("Offer Received").Range("E8").PasteSpecial xlPasteValues
    Intersect(.AutoFilter.Range.Offset(1), .Range("H:h")).Copy
    Sheets("Offer Received").Range("E10").PasteSpecial xlPasteValues
    Intersect(.AutoFilter.Range.Offset(1), .Range("I:i")).Copy
    Sheets("Offer Received").Range("E12").PasteSpecial xlPasteValues
    Intersect(.AutoFilter.Range.Offset(1), .Range("J:j")).Copy
    Sheets("Offer Received").Range("E14").PasteSpecial xlPasteValues
    Intersect(.AutoFilter.Range.Offset(1), .Range("K:k")).Copy
    Sheets("Offer Received").Range("E18").PasteSpecial xlPasteValues
    Intersect(.AutoFilter.Range.Offset(1), .Range("L:l")).Copy
    Sheets("Offer Received").Range("E20").PasteSpecial xlPasteValues
    Intersect(.AutoFilter.Range.Offset(1), .Range("M:m")).Copy
    Sheets("Offer Received").Range("H8").PasteSpecial xlPasteValues
    Intersect(.AutoFilter.Range.Offset(1), .Range("N:n")).Copy
    Sheets("Offer Received").Range("H10").PasteSpecial xlPasteValues
    Intersect(.AutoFilter.Range.Offset(1), .Range("P:p")).Copy
    Sheets("Offer Received").Range("H14").PasteSpecial xlPasteValues
    Intersect(.AutoFilter.Range.Offset(1), .Range("Q:q")).Copy
    Sheets("Offer Received").Range("H20").PasteSpecial xlPasteValues
    Intersect(.AutoFilter.Range.Offset(1), .Range("R:r")).Copy
    Sheets("Offer Received").Range("B23").PasteSpecial xlPasteValues
    Intersect(.AutoFilter.Range.Offset(1), .Range("S:s")).Copy
    Sheets("Offer Received").Range("B25").PasteSpecial xlPasteValues
    Intersect(.AutoFilter.Range.Offset(1), .Range("T:t")).Copy
    Sheets("Offer Received").Range("B27").PasteSpecial xlPasteValues
    Intersect(.AutoFilter.Range.Offset(1), .Range("U:u")).Copy
    Sheets("Offer Received").Range("B29").PasteSpecial xlPasteValues
    Intersect(.AutoFilter.Range.Offset(1), .Range("V:v")).Copy
    Sheets("Offer Received").Range("E23").PasteSpecial xlPasteValues
    Intersect(.AutoFilter.Range.Offset(1), .Range("W:w")).Copy
    Sheets("Offer Received").Range("E25").PasteSpecial xlPasteValues
    Intersect(.AutoFilter.Range.Offset(1), .Range("X:x")).Copy
    Sheets("Offer Received").Range("E27").PasteSpecial xlPasteValues
    Intersect(.AutoFilter.Range.Offset(1), .Range("Y:y")).Copy
    Sheets("Offer Received").Range("E29").PasteSpecial xlPasteValues
    Sheets("HR Advice & Admin").Select
    Selection.AutoFilter
    Sheets("Offer Received").Select
    Application.CutCopyMode = False
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,440
Office Version
365
Platform
Windows
That looks better. :)
But please define "not working"
 

eviehc123

New Member
Joined
Jan 21, 2019
Messages
30
An error comes up and highlights the top line of code! ?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,440
Office Version
365
Platform
Windows
What is the error number & message?
 

eviehc123

New Member
Joined
Jan 21, 2019
Messages
30
Hi the error is:
'Compile error:
Invalid or unqualified reference'

It highlights the following:

Rich (BB code):
 .Range("$A$1:$AS$286").AutoFilter Field:=1, Criteria1:=FilterString
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,440
Office Version
365
Platform
Windows
Remove the . from in front of Range
 

eviehc123

New Member
Joined
Jan 21, 2019
Messages
30
Sorry yeah! Now the error: Run-time error '424':
Object required

highlighting the row below

Rich (BB code):
Sheets("HR Advice & Admin").Select
    FilterString = Sheets("Offer Received").Range("G5").Value
    Range("$A$1:$AS$286").AutoFilter Field:=1, Criteria1:=FilterString
    Intersect(AutoFilter.Range.Offset(1), Range("B:b")).Copy
    Sheets("Offer Received").Range("B5").PasteSpecial xlPasteValues
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,440
Office Version
365
Platform
Windows
Try
Code:
Sub Searchproper()
'
' copyandpaste Macro
'


'
    With Sheets("HR Advice & Admin")
      FilterString = Sheets("Offer Received").Range("G5").Value
      .Range("$A$1:$AS$286").AutoFilter Field:=1, Criteria1:=FilterString
      Intersect(.AutoFilter.Range.Offset(1), .Range("B:B")).Copy
      Sheets("Offer Received").Range("B5").PasteSpecial xlPasteValues
      Intersect(.AutoFilter.Range.Offset(1), .Range("C:c")).Copy
      Sheets("Offer Received").Range("B10").PasteSpecial xlPasteValues
      Intersect(.AutoFilter.Range.Offset(1), .Range("D:d")).Copy
      Sheets("Offer Received").Range("B12").PasteSpecial xlPasteValues
      Intersect(.AutoFilter.Range.Offset(1), .Range("E:e")).Copy
      Sheets("Offer Received").Range("B14").PasteSpecial xlPasteValues
      Intersect(.AutoFilter.Range.Offset(1), .Range("F:f")).Copy
      Sheets("Offer Received").Range("B16").PasteSpecial xlPasteValues
      Intersect(.AutoFilter.Range.Offset(1), .Range("G:g")).Copy
      Sheets("Offer Received").Range("E8").PasteSpecial xlPasteValues
      Intersect(.AutoFilter.Range.Offset(1), .Range("H:h")).Copy
      Sheets("Offer Received").Range("E10").PasteSpecial xlPasteValues
      Intersect(.AutoFilter.Range.Offset(1), .Range("I:i")).Copy
      Sheets("Offer Received").Range("E12").PasteSpecial xlPasteValues
      Intersect(.AutoFilter.Range.Offset(1), .Range("J:j")).Copy
      Sheets("Offer Received").Range("E14").PasteSpecial xlPasteValues
      Intersect(.AutoFilter.Range.Offset(1), .Range("K:k")).Copy
      Sheets("Offer Received").Range("E18").PasteSpecial xlPasteValues
      Intersect(.AutoFilter.Range.Offset(1), .Range("L:l")).Copy
      Sheets("Offer Received").Range("E20").PasteSpecial xlPasteValues
      Intersect(.AutoFilter.Range.Offset(1), .Range("M:m")).Copy
      Sheets("Offer Received").Range("H8").PasteSpecial xlPasteValues
      Intersect(.AutoFilter.Range.Offset(1), .Range("N:n")).Copy
      Sheets("Offer Received").Range("H10").PasteSpecial xlPasteValues
      Intersect(.AutoFilter.Range.Offset(1), .Range("P:p")).Copy
      Sheets("Offer Received").Range("H14").PasteSpecial xlPasteValues
      Intersect(.AutoFilter.Range.Offset(1), .Range("Q:q")).Copy
      Sheets("Offer Received").Range("H20").PasteSpecial xlPasteValues
      Intersect(.AutoFilter.Range.Offset(1), .Range("R:r")).Copy
      Sheets("Offer Received").Range("B23").PasteSpecial xlPasteValues
      Intersect(.AutoFilter.Range.Offset(1), .Range("S:s")).Copy
      Sheets("Offer Received").Range("B25").PasteSpecial xlPasteValues
      Intersect(.AutoFilter.Range.Offset(1), .Range("T:t")).Copy
      Sheets("Offer Received").Range("B27").PasteSpecial xlPasteValues
      Intersect(.AutoFilter.Range.Offset(1), .Range("U:u")).Copy
      Sheets("Offer Received").Range("B29").PasteSpecial xlPasteValues
      Intersect(.AutoFilter.Range.Offset(1), .Range("V:v")).Copy
      Sheets("Offer Received").Range("E23").PasteSpecial xlPasteValues
      Intersect(.AutoFilter.Range.Offset(1), .Range("W:w")).Copy
      Sheets("Offer Received").Range("E25").PasteSpecial xlPasteValues
      Intersect(.AutoFilter.Range.Offset(1), .Range("X:x")).Copy
      Sheets("Offer Received").Range("E27").PasteSpecial xlPasteValues
      Intersect(.AutoFilter.Range.Offset(1), .Range("Y:y")).Copy
      Sheets("Offer Received").Range("E29").PasteSpecial xlPasteValues
      .AutoFilterMode = False
    End With
    Sheets("Offer Received").Select
    Application.CutCopyMode = False
End Sub
 

Forum statistics

Threads
1,082,587
Messages
5,366,486
Members
400,894
Latest member
frog9000

Some videos you may like

This Week's Hot Topics

Top