copy and paste visible rows

eviehc123

New Member
Joined
Jan 21, 2019
Messages
32
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..

Code:
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Try
Code:
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
 
Upvote 0
Hi thank you,

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

Thanks,
Eve
 
Upvote 0
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
 
Upvote 0
How about
Code:
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
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,223,264
Messages
6,171,081
Members
452,377
Latest member
bradfordsam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top