CODEWITHSUNDEEP
excelvba
Big D
Big D

Reputation: 11

Excel Macro to copy worksheet to new worksheet paste values only

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

Upvotes: 1

Views: 1372

Answers (2)

Gary's Student
Gary's Student

Reputation: 96771

I use something like this:

Sub KopyKat()
Dim s1 As Worksheet, s2 As Worksheet
Dim r As Range, addy As String

Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")

For Each r In s1.UsedRange
    If r.Value <> "" Then
        If Not r.HasFormula Then
            addy = r.Address
            r.Copy s2.Range(addy)
        End If
    End If
Next r

End Sub

Upvotes: 0

Dakota
Dakota

Reputation: 474

If the data is contiguous, consider creating a new sheet, selecting and copying the range of data, and pasting onto the new sheet using the below code.

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

Upvotes: 0

Related Questions