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.
Share Improve this question edited Jan 19 at 19:38 braX 11.8k5 gold badges22 silver badges37 bronze badges asked Jan 19 at 19:38 Zachary NaylorZachary Naylor 31 bronze badge 1- 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
Reset to default 0Option 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