0

Currently I have a copy and paste loop that uses references from a table to copy and paste across varying files and sheets. The one issue I am having is that it will paste the formulas, when I just need the values. I tried every stackoverflow and youtube tutorial and tried every place to put .PasteSpecial xl:=PasteValues, but nothing works for me. Do you know where or how I would include paste value command into this code?

Public Sub Copy_Paste_NAVRec()
    BegRow = ThisWorkbook.Sheets("Merge").Range("C12").Value
    EndRow = ThisWorkbook.Sheets("Merge").Range("C13").Value
    RowWidth = ThisWorkbook.Sheets("Merge").Range("C14").Value
    
    Dim wsMerge As Worksheet, i As Long, rw As Range
    
    Set wsMerge = ThisWorkbook.Sheets("Merge") 'or whatever
    
    Set rw = wsMerge.Range(RowWidth) 'your first row of copy/paste settings
    
    Debug.Print BegRow
    Debug.Print EndRow
    Debug.Print RowWidth
    
    For i = BegRow To EndRow 'or however many rows of settings you have
        
        Workbooks(rw.Cells(1).Value).Worksheets(rw.Cells(2).Value). _
            Range(rw.Cells(3).Value).Copy _
            Workbooks(rw.Cells(4).Value).Worksheets(rw.Cells(5).Value). _
            Range(rw.Cells(6).Value)
    
        Set rw = rw.Offset(1) 'next row of copy/paste range settings
        
    Next i

End Sub
mmb53
  • 7
  • 5
  • Get rid of the `_` after `.Copy`. Then `Workbooks(rw.Cells(4).Value).Worksheets(rw.Cells(5).Value). _ Range(rw.Cells(6).Value).PasteSpecial xlPasteValues`. – BigBen Sep 08 '22 at 15:06

0 Answers0