Copy values only when using a filter

Closed
Debs269 Posts 23 Registration date Monday October 15, 2012 Status Member Last seen August 18, 2016 - Aug 18, 2016 at 05:10 AM
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 - Aug 18, 2016 at 08:13 AM
Hello,

VBA below, this is doing exactly what I want... however I can not get it to copy values only...not the formulas and a list which is in a couple of cells

Can anyone help please ..


Sub CopyingDataToNewItems()

Dim FilterRng As Range
Dim CopyRng As Range
Dim Dest As Range
With Sheets("Input (2)")
Set FilterRng = .Range("A5", .Range("A" & Rows.Count).End(xlUp))
Set CopyRng = .Range("A5", .Range("A" & Rows.Count).End(xlUp))
End With
With Sheets("New Items Added")
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1)
End With
FilterRng.AutoFilter Field:=1, Criteria1:=">1"
CopyRng.SpecialCells(xlCellTypeVisible).EntireRow.Copy destination:=Dest

FilterRng.AutoFilter

End Sub


Many Thanks


2 replies

vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 250
Aug 18, 2016 at 07:17 AM
Hello Debs269,

Try changing this part:-

FilterRng.AutoFilter Field:=1, Criteria1:=">1" 
CopyRng.SpecialCells(xlCellTypeVisible).EntireRow.Copy Destination:=Dest


to

FilterRng.AutoFilter Field:=1, Criteria1:=">1" 
CopyRng.SpecialCells(xlCellTypeVisible).EntireRow.Copy Destination:=Dest.PasteSpecial xlPasteValues


It should now copy/paste values only.

Cheerio,
vcoolio.
0
Debs269 Posts 23 Registration date Monday October 15, 2012 Status Member Last seen August 18, 2016 2
Aug 18, 2016 at 07:34 AM
Thanks getting a Syntax error? can you help
0
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 250
Aug 18, 2016 at 08:13 AM
Hello Debs,

I thought that may be the case!

Could you please upload a sample of your work book to a free file sharing site such as DropBox, ge.tt or SpeedyShare and then post the link to your file back here. Please use dummy data and include the code that you have in a standard module in the work book.

This way, it will be easier to try and decipher what the problem is.

Cheerio,
vcoolio.
0