Copy values only

Hi,

Am new to the forum and just need some help with this coding please.

At present I have a Data Input sheet, then info copies over onto various other sheets using this VBA coding (this is just a section of it)

Sheets("Niche Floor").Cells.ClearContents

Dim i3 As Long, iMatches3 As Long
Dim aTokens3() As String: aTokens3 = Split("Niche", ",")
For Each Cell In Sheets("Data input").Range("a4:a9999")
    If (Len(Cell.Value) = 0) Then Exit For
        For i3 = 0 To UBound(aTokens3)
            If InStr(1, Cell.Value, aTokens3(i3), vbTextCompare) Then
                iMatches3 = (iMatches3 + 1)
                Sheets("data input").Rows(Cell.Row).Copy Sheets("Niche Floor").Rows(iMatches3)
            End If
        Next
Next

Sheets("Niche Floor").Select
    ActiveSheet.AutoFilterMode = False
    ActiveWindow.FreezePanes = False
    Sheets("Niche Floor").Select
    ActiveSheet.Range("a1").Select
    Selection.EntireRow("1:3").Select
    Selection.Insert Shift:=xlDown
    Sheets("data input").Select
    Rows("1:3").Select
    Selection.Copy
    Sheets("Niche Floor").Select
    ActiveSheet.Range("a1").Select
    ActiveSheet.Paste
    ActiveSheet.Range("D4").Select
    ActiveWindow.FreezePanes = True
    ActiveSheet.Rows("3:3").AutoFilter
    ActiveSheet.Columns.EntireColumn.AutoFit
    ActiveSheet.Columns("A:A").Select
    Selection.EntireColumn.Hidden = True

This all works spot on, but I also have a version where the data entry is links to other workbooks, so the data is not just simple text and numbers.

Can anyone please suggest an amend to the above that would copy values only?

My problem is this part...

Dim i3 As Long, iMatches3 As Long
Dim aTokens3() As String: aTokens3 = Split("Niche", ",")
For Each Cell In Sheets("Data input").Range("a4:a9999")
    If (Len(Cell.Value) = 0) Then Exit For
        For i3 = 0 To UBound(aTokens3)
            If InStr(1, Cell.Value, aTokens3(i3), vbTextCompare) Then
                iMatches3 = (iMatches3 + 1)
                Sheets("data input").Rows(Cell.Row).Copy Sheets("Niche Floor").Rows(iMatches3)
            End If
        Next
Next

Doesn’t ever include .paste so I haven’t got anything to simply replace with .pastespecial xlpastevalues, otherwise it would be fairly simple.

Any suggestions – think it’s this line that is performing the "pasting" function - Sheets("data input").Rows(Cell.Row).Copy Sheets("Niche Floor").Rows(iMatches3)

Thanks in advance,

Ross

Moderator Note:

Pls use code tags around your code next time as per forum rules.