Copy values only when using a filter

[Closed]
Report
Posts
23
Registration date
Monday October 15, 2012
Status
Member
Last seen
August 18, 2016
-
Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
-
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

Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
239
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.
Posts
23
Registration date
Monday October 15, 2012
Status
Member
Last seen
August 18, 2016
2
Thanks getting a Syntax error? can you help
Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
239
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.