I have a macro that is copying only visible items from a page that is using a filtering macro called (ws1) to a hidden page called (ws2). On button click it is unhiding ws2, and copying it, but some of the cells are retaining the formula and referencing the source workbook. I'd like to just use xLpastespecial to only copy the values but I can't seem to get the range correct for the reference or I'm not inserting it right. Below is what I was using without xLpasetspecial that just gets me the copied doc but with the reference formula copied as well. Any suggestions would be awesome!

VBA Macro Below:

Sub TestThat()

Dim i As Integer
Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Estimated Bill of Materials")
Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("Copyable ROM")
Dim sourceRange As Range: Set sourceRange = ws1.Range("B2:C7")

sourceRange.Copy
ws2.Visible = xlSheetVisible
ws2.Activate

Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False



ws2.Range("B12:J100").Clear


    For i = 12 To ws1.Range("B100").End(xlUp).Row
        If ws1.Cells(i, 9) = "*" Then ws1.Range(ws1.Cells(i, 1), ws1.Cells(i, 7)).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 2).End(xlUp).Row + 1)
    Next i
    ws2.Copy
    ws2.Visible = xlSheetHidden
    
End Sub

Tried copying an already filtered worksheet into a new workbook. Wanted just the values but got the formula generated values instead.

I have a macro that is copying only visible items from a page that is using a filtering macro called (ws1) to a hidden page called (ws2). On button click it is unhiding ws2, and copying it, but some of the cells are retaining the formula and referencing the source workbook. I'd like to just use xLpastespecial to only copy the values but I can't seem to get the range correct for the reference or I'm not inserting it right. Below is what I was using without xLpasetspecial that just gets me the copied doc but with the reference formula copied as well. Any suggestions would be awesome!

VBA Macro Below:

Sub TestThat()

Dim i As Integer
Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Estimated Bill of Materials")
Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("Copyable ROM")
Dim sourceRange As Range: Set sourceRange = ws1.Range("B2:C7")

sourceRange.Copy
ws2.Visible = xlSheetVisible
ws2.Activate

Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False



ws2.Range("B12:J100").Clear


    For i = 12 To ws1.Range("B100").End(xlUp).Row
        If ws1.Cells(i, 9) = "*" Then ws1.Range(ws1.Cells(i, 1), ws1.Cells(i, 7)).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 2).End(xlUp).Row + 1)
    Next i
    ws2.Copy
    ws2.Visible = xlSheetHidden
    
End Sub

Tried copying an already filtered worksheet into a new workbook. Wanted just the values but got the formula generated values instead.

• excel
• vba

• Your copying within the loop at the end - that will copy the formula over as well. – Darren Bartrup-Cook Commented Jan 20 at 9:02

1 Answer 1

Option Explicit

Sub TestThat2()

    Dim ws1 As Worksheet, ws2 As Worksheet, rngTo As Range
    Dim r As Long, lastrow As Long

    With ThisWorkbook
        Set ws1 = .Sheets("Estimated Bill of Materials")
        Set ws2 = .Sheets("Copyable ROM")
    End With
    
    ' destination sheet
    With ws2
        .Visible = xlSheetVisible
        .Range("B2:C7").Value2 = ws1.Range("B2:C7").Value2
        .Range("B12:J100").Clear
        Set rngTo = .Cells(.Rows.Count, "B").End(xlUp).Offset(, -1)
    End With
    
    ' source sheet
    With ws1
        lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
        For r = 12 To lastrow
            If .Cells(r, "I") = "*" Then
                ' copy cols A-G to new row
                Set rngTo = rngTo.Offset(1)
               .Cells(r, 1).Resize(, 7).Copy rngTo
                rngTo.Resize(, 7).Value2 = rngTo.Resize(, 7).Value2
            End If
        Next
    End With
    ws2.Copy
    ws2.Visible = xlSheetHidden
    
End Sub