I am working on a Macro in Excel that will make a copy of the current worksheet and paste the values into a new worksheet. The worksheet name would be the same just with a number after it [ie Sheet, Sheet1(2)]
My code does this correctly except that it copies and pastes everything to Sheet1(2). I only want it to paste the values (not formulas) from Sheet1 to Sheet1(2). I'm a novice at vba at best so any suggestions are greatly appreciated.
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
Sheets("New Sheet Name").Cells.Value = Sheets("New Sheet Name").Cells.Value
? – BruceWayne