VBA - Copying formulas from a workbook - Paste Values

[rniculae]

Hello,

I've recently created this macro that opens, one by one, all excels in a designated folder and copies the rows from worksheet 2, to a new workbook. Every new excel rows are copied below the one before.

Well, now i want to paste only the values which contains formulas.

I've tried to use .PasteSpecial xlPasteValues and it's not working.
Can you help me?

Thanks,
Razvan

Public Sub MergeWorkbooks()
    Dim ROOT_FOLDER As String
    Dim wbTarget As Workbook
    Dim Filename As String
    Dim filenames As Variant
    Dim numrows As Long
    Dim nextrow As Long
    ROOT_FOLDER = Sheets("Concatenare fisiere XLS magazin").Range("D11").Value
    
    Set wbTarget = Workbooks.Add
        
    Filename = Dir(ROOT_FOLDER & "*.xlsx")
    ReDim filenames(1 To 1)
     
    nextrow = 1
    rand = 3
    Do While Filename <> ""
         
        Workbooks.Open ROOT_FOLDER & Filename
        With ActiveWorkbook
             
            With .Worksheets(2)
                 
                numrows = .Cells(.Rows.Count, "A").End(xlUp).Row
                If nextrow <> 1 Then rand = 2
                .Rows(rand).Resize(numrows - rand).Copy.PasteSpecial wbTarget.Worksheets(1).Cells(nextrow, "A")
                nextrow = nextrow + numrows - rand
            End With
             
            .Close SaveChanges:=False
        End With
         
        Filename = Dir
    Loop
     
     'do something with wbTarget
End Sub

 

[yky]

You may want to consider selecting cells that contain a formula. The following code select cells that contain a formula from range("B2:J25") and paste them to sheets(3), starting form A1 down.

Dim rng1 As Range
Dim cell As Range
Dim rowno As Integer

rowno = 1

Set rng1 = ThisWorkbook.Sheets(2).Range("B2:J25").SpecialCells(xlCellTypeFormulas)

For Each cell In rng1

ThisWorkbook.Sheets(3).Cells(rowno, 1) = cell

rowno = rowno + 1
Next