copy and paste visible rows

[eviehc123]

Hi can someone help with the following code?

I want to filter the "HR Advice & admin" sheets to display all rows which have no data in collumn ("N") then paste all rows displayed into "Sheet1". I want to exclude the top row to be copied and pasted into the second row on "Sheet1".

What I have so far..

Sub Button6_Click()
'
' Macro7 Macro
'


'
ActiveSheet.Range("N17").Value = ("")
With Sheets("HR Advice & Admin")
      FilterString = Sheets("Menu").Range("N17").Value
      .Range("$N$1:$NS$286").AutoFilter Field:=1, Criteria1:=FilterString
      ActiveSheet.Range.SpecialCells(xlCellTypeVisible).Copy
      Sheets("Sheet1").Range("A1").PasteSpecial xlPasteValues
End With
Sheets("HR Advice & Admin").AutoFilterMode = False
End Sub

Thanks,
Eve

 

[Fluff]

Try

Sub Button6_Click()
   With Sheets("HR Advice & Admin")
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("[COLOR=#ff0000]A1:Z1[/COLOR]").AutoFilter Field:=14, Criteria1:=""
      .AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Copy
      Sheets("Sheet1").Range("A1").PasteSpecial xlPasteValues
      .AutoFilterMode = False
   End With
End Sub

Change the part in red to cover the entire header row

 

[eviehc123]

Hi thank you,

How do I make sure the data is pasted in the second row and not over the headers?

Thanks,
Eve

 

[eviehc123]

Sorted now! Was just being stupid!

I have realised I only want to copy certain collumns of data. "A,C,D,F,H,N,Q,AC,AE,AH,AM,AN,AO,AP,AQ". Is there a way of incorporating this in?

Thanks

 

[Fluff]

How about

Sub Button6_Click()
   Dim FilterString As String
   FilterString = Sheets("Menu").Range("N17").Value
   With Sheets("HR Advice & Admin")
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("A1:AQ1").AutoFilter Field:=1, Criteria1:=FilterString
      Intersect(.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible), .Range("A:A,C:D,F:F,H:H,N:N,Q:Q,AC:AC,AE:AE,AH:AH,AM:AQ")).Copy
      Sheets("Sheet1").Range("A2").PasteSpecial xlPasteValues
      .AutoFilterMode = False
   End With
End Sub

 

[eviehc123]

Works great! Thank you

 

[Fluff]

You're welcome & thanks for the feedback