For loop - Cannot paste as values

[stormseeker75]

I'm running a For loop to copy and paste some data to additional cells off to the right. No matter what I do, the PasteSpecial funtion does not work. See the one xlPasteValues at the bottom. I've tried every versions of the syntax for PasteSpecial I can find and they all throw Run Time Error 1004. PasteSpecial method of Worksheet class failed. Does that mean it has something to do with my ActiceSheet.Pastespecial? If so, how do I get it to paste in the scenario below?

Sub FindValueAndSelectRight()
    Dim ws As Worksheet
    Dim cell As Range
    Dim searchValue As String
    Dim searchRange As Range

    ' Set the worksheet and search range
    Set ws = ThisWorkbook.Sheets("SALES_SHEET_MASTER")
    Set searchRange = ws.Range("S1:S1171")
    
    ' Define the value to search for
    searchValue = "YourValue"
    
    ' Loop through each cell in the search range
    For Each cell In searchRange
        If cell = 1 Then
            ' Select the cell to the right of the found value
            cell.Select
            ActiveCell.Offset(rowOffset:=0, columnOffset:=-1).Activate
            Application.CutCopyMode = False
            Selection.Copy
            ActiveCell.Offset(rowOffset:=0, columnOffset:=3).Activate
            ActiveSheet.Paste
            ActiveCell.Offset(rowOffset:=27, columnOffset:=-12).Activate
            Application.CutCopyMode = False
            Selection.Copy
           ActiveCell.Offset(rowOffset:=-27, columnOffset:=13).Activate
           ActiveSheet.Paste
            ActiveCell.Offset(rowOffset:=27, columnOffset:=-12).Activate
            Application.CutCopyMode = False
           Selection.Copy
         ActiveCell.Offset(rowOffset:=-27, columnOffset:=13).Activate
          ActiveSheet.Paste
' Next Copy/Pasta
            ActiveCell.Offset(rowOffset:=13, columnOffset:=-14).Activate
            Application.CutCopyMode = False
           Selection.Copy
         ActiveCell.Offset(rowOffset:=-13, columnOffset:=15).Activate
          ActiveSheet.Paste
' Next Copy/Pasta
            ActiveCell.Offset(rowOffset:=0, columnOffset:=-14).Activate
            Application.CutCopyMode = False
           Selection.Copy
         ActiveCell.Offset(rowOffset:=0, columnOffset:=15).Activate
          ActiveSheet.Paste
          ActiveCell.UnMerge
' Next Copy/Pasta
            ActiveCell.Offset(rowOffset:=1, columnOffset:=-15).Activate
            Application.CutCopyMode = False
           Selection.Copy
         ActiveCell.Offset(rowOffset:=-1, columnOffset:=16).Activate
          ActiveSheet.Paste
          ActiveCell.UnMerge
' Next Copy/Pasta
            ActiveCell.Offset(rowOffset:=4, columnOffset:=-16).Activate
            Application.CutCopyMode = False
           Selection.Copy
         ActiveCell.Offset(rowOffset:=-4, columnOffset:=17).Activate
          ActiveSheet.Paste
          ActiveCell.UnMerge
' Next Copy/Pasta
            ActiveCell.Offset(rowOffset:=6, columnOffset:=-17).Activate
            Application.CutCopyMode = False
           Selection.Copy
         ActiveCell.Offset(rowOffset:=-6, columnOffset:=18).Activate
    [COLOR=rgb(247, 218, 100)]      ActiveSheet.PasteSpecial (xlPasteValues)[/COLOR]
          ActiveCell.UnMerge

        Else
        
        End If
    Next cell
End Sub

 

[Candyman8019]

Is this particular cell locked on a protected sheet?

Have you tried:
ActiveSheet.PasteSpecial Paste:=xlPasteValues

 

[stormseeker75]

I get a different error now. Still run-time 1004. Text now says "Application-defined or object-defined error"

 

[stormseeker75]

This seemed to do the trick. No idea why.

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

 

[bobsan42]

This seemed to do the trick. No idea why.

because Selection is a Range object, as opposed to Activesheet which is a Sheet object.

 

[stormseeker75]

That makes sense based on the error I was getting. I am learning so thank you. That actually helped a ton.