How to create macro to copy worksheet and paste values only

[Big Daryl]

I am trying to create a macro that copies the active worksheet to a new worksheet and paste values only. For instance, Sheet1 is the active sheet. I want to make Sheet1(2) with values only. Additionally, I evaluate each row of the Sheet1(2) to look for a value of X in a cell in each row. If there is an X, then the row is deleted.

My attached code kinda works. It copies Sheet1 to Sheet1(2) and deletes all of the rows with an X in a particular cell, the problem is it is a duplicate copy with the formulas. I just want it to copy and paste the values (so it would be a static worksheet of values only, no formulas).

I'm not the best at programming so any suggestions I really appreciate.

Sub SPACER_Button4_Click()
' Compile Button to Generate Quote
'
'variables definitions
ActiveSheetValue = ActiveSheet.Name
'
'This section creates a copy of the active worksheet and names it with the next corresponding number.

Sheets(ActiveSheetValue).Copy After:=Sheets(ActiveSheetValue)
    
'This section should look for X value in each row, column 4. If value equals X, it deletes the row on the copied sheet

Dim i As Integer
i = 26
Do Until i > 300
    If ActiveSheet.Cells(i, 11).Value = "X" Then
        Rows(i).Delete
        Skip = True
    End If
    '
    If Skip = False Then
        i = i + 1
    End If
    '
    Skip = False
Loop

'This part hides columns on Right K thru R of new copied sheet

Sheets(ActiveSheet.Name).Range("K:R").EntireColumn.Hidden = True

'
End Sub

[Luuk]

You seems to have gotten pretty far so far (based on the 'I'm not the best at programming')

For the question 'How to paste values in Excel', I would like to suggest to Google for 'excel macro paste as values'.

It will for sure give some examples of how to do that!
(Ok, I know, I CAN put this code here but … 😊)

Secondly: why are you copying a whole sheet and after that deleting certain rows?
Is it not simples to copy just the rows you need?

[SioSio]

After copying a sheet, what about "clear format" of all cells in the copied sheet?

Cells.ClearFormats