Copy Values instead of Formula

[GonzoB]

Hi Helpers,

I have a VBA to copy the distinct values of a column from Sheet1 to Sheet2:

Sheets("Sheet1").Range("U2:U" & Sheet1Row).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("Sheet2").Range("A2"), Unique:=True

The issue I have is the source column contains formula results and not values (this I don't really want to change) therefore to the Sheet2 target column #N/A is added.

Can you tell how to copy the values instead of the formula using the above code? Is there something like CopySpecial similar to PasteSpecial? That would solve my problem I guess.

Thanks

 

[Alex Blakenburg]

There has to be something else going on, you might need to give us an XL2BB of your data.
Advanced Filter only brings across values and not formulas. If it is bringing over N/A it is more likely that you have that in your source data that you are filtering.

 

[GonzoB]

Hi Alex,

yes, in my source data (Sheet1, U column) I have formulas. Is there a way to copy over the results of the formulas using Advanced Filter?

 

[Alex Blakenburg]

I have to be misinterpreting what you are saying.
Advanced Filter using the Copy to Option is similar to PasteSpecial Values followed by PasteSpecial Formats.
For a reference see Contextures Advanced Filter Page here: Excel Advanced Filter Macro Examples Send Data to New Sheet
You seem to be saying that is what you want ie "copy over results of the formulas"

If you want the equivalent of PasteSpecial Formulas then you can't use the Copy option of Advanced Filter.
(the above link covers that too)

If you are saying that it is pasting formulas then there is something else going on.
You would need to show us all your code.
Ideally also an XL2BB of the source and the XLBB of the results. Alternatively put a sample of your spreadsheet on a sharing platform making it available to anyone with the link.
Test the sample of the spreadsheet to make sure it is producing what you consider to be the error.