最新消息:雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客

excel - Macro Help: Workbook to New Doc Copying Only the Cell Values - Stack Overflow

programmeradmin0浏览0评论

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
Add a comment  | 

1 Answer 1

Reset to default 0
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
发布评论

评论列表(0)

  1. 暂无评论