How to not copy headers in vba

[eviehc123]

Hi all!

I am trying to copy values in cells from one worksheet to another after filtering. My problem is that the code below is copying the headers when a cell is blank. I would like it to just keep the cell blank instead in the worksheet where the data is being pasted. Please see my code below:

    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 _
    Sheets("Offer Received").Range("B5")
    Sheets("HR Advice & Admin").Select
    Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
    Sheets("Offer Received").Range("B10")
    Sheets("HR Advice & Admin").Select

It may be worth mentioning at the point that I also just want to copy and paste the text not the formatting etc.

Thanks in advance for your help.
Eve

 

[steve the fish]

Hi. Your paste is a little strange. Why are you hardcoding the ranges B5 and B10 in the macro? If there are more than 6 values from the autofilter you will overwrite them with the 2nd paste.

 

[Fluff]

Hi & welcome to MrEexcel
How about

Sub eviehc123()
   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
   End With
End Sub

 

[eviehc123]

Hi. I have a search button on my Offers Received page.

The button has a macro to search based on the number entered into the cell next to it.

I want to automatically filter and copy the cells to the relevant cells on the Offers Received page.

I want to only copy/paste the cells that the filter brings up (which will only ever be one row of data) and I don't want to copy the formatting etc.

The issue I'm getting is if a cell is blank in the row of data, it is copying the column header instead.

Thanks,
Eve

 

[eviehc123]

Hi & welcome to MrEexcel
How about

Sub eviehc123()
   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
   End With
End Sub

Thanks! I'll give this a go!