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
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
Code:
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