Macro to copy as values distinct values from one Excel tab to another

I have an excel workbook with a macro that does almost everything I want it to do. This macro copies data from a range on one tab, then pastes the distinct values on another tab.

What I need help with is changing the macro to paste as values. The data being copied is a formula and formatted differently than the destination.

The below macro is based off the one Gary's Student was nice enough to create to answer the question at this link: Macro to copy distinct values from one Excel sheet to another

Sub Update()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Copy Tab")
    Set s2 = Sheets("Paste Tab")
    s1.Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row).Copy s2.Range("A9")
    s2.Range("A8:A" & Cells(Rows.Count, "A").End(xlUp).Row).RemoveDuplicates Columns:=1, Header:=xlNo
End Sub

Solution 1:

Change the Copy line:

s1.Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row).Copy s2.Range("A9")

To equate the values:

s2.Range("A9").Resize(s1.Cells(s1.Rows.Count, "B").End(xlUp).Row-1).Value = s1.Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row).Value

Now we avoid the clipboard and we only move the values.

Solution 2:

What you want to do is record a new macro, then perform the action once manually, stop the recording and look at the new macro that was recorded. It will tell you exactly what code to use.

Why do I tell you the above? It will help you become a good VBA developer without having to search the whole internet for every bit of code.

If you do the above, you'll see that you get the following macro:

Range("A8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

So your code becomes:

Sub Update()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Copy Tab")
    Set s2 = Sheets("Paste Tab")
    s1.Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row).Copy s2.Range("A9")

    s2.Range("A8:A" & Cells(Rows.Count, "A").End(xlUp).Row).Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

End Sub