Your question is not entirely clear, but I can tell you not to use Paste!
Have a look at this code snippet...
Public Sub test()
Dim SourceRange As Range, TargetRange As Range
Set SourceRange = ThisWorkbook.Sheets(1).UsedRange
Set TargetRange = ThisWorkbook.Sheets(2).Range(SourceRange.Address)
TargetRange.Value = SourceRange.Value
End Sub
To see it working, create a workbook with two sheets.
On the first sheet enter some formulae. Run the code - the values calculated by the formulae will be on Sheet 2 … as if you had used Paste, Special, Values. Just much quicker and much more robustly.
To do the file handling bit just use the FileSystem object in VBA - example here
VBA List Files in Folder - Automate Excel[
^]
If you get stuck after you have written some of your own code then by all means come back with a specific question