PDA

View Full Version : Paste as Unique values and paste dynamic cell formulas too



RINCONPAUL
07-30-2016, 02:03 PM
I have a sub Unique2 that pastes values from specified columns to another sheet at 2 sec intervals, leaving blank columns in between. In the left adjacent blank column to each group, I want to paste this formula, "=IF(LINEST(^^$3:H??)>0,"UP","DOWN")"

Because the pasting table is ever increasing in length with each paste the '??' row would be the current paste row number and '^^' the col value left adjacent to the groups right col value. In this case cols H, P & X. This is the code for the paste of Unique values. Now I can get round this by having cols H, P & X prepopulated with the formula "=IF(LINEST(^^$3:H??)>0,"UP","DOWN")", but I figure that's going to sap unnecessary processing time?


Sub Unique2()
Sheets("GRAPHS").Range("C1:G20000").AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Sheets("Unique").Range( _
"C2:G2"), Unique:=True
Sheets("GRAPHS").Range("K1:O20000").AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Sheets("Unique").Range( _
"K2:O2"), Unique:=True
Sheets("GRAPHS").Range("S1:W20000").AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Sheets("Unique").Range( _
"S2:W2"), Unique:=True