Problem pasting values from one Excel Workbook to others

Question

I want to copy the same values from one workbook to another 30 workbooks. I have found a macro that open and copies all the values of the files of the first workbook, but I cannot paste any value, and I am not sure why. Also I don't know how I could put the name of the workbook that I want to paste because there are a lot of names.

This would be the problem, I think. "reports.xlms" is only one name of the files, there are other files with other names. Is there a way to copy and paste the values without specifying the name of the worksheets? maybe only saying the number, for example, worksheet(1), like this:

Workbooks("Reports.xlsm").Worksheets("Data").Range("A2:D9").PasteSpecial

Also it doesn't copy any value , even when I use this simple code:

Range("B2:B5").Select
Selection.Copy
Sheets("Hoja2").Select
Range("B2").Select
ActiveSheet.Paste

Here is my code:

Sub AbrirArchivos()
 Dim Archivos As String

 'Paso 2: Especificar una carpeta y el tipo de archivo que buscamos
 'en este caso la carpeta se llama "temporal" y el tipo de dato es "xlsx"

 Archivos = Dir("C:\Users\fernandofernandez\Desktop\Prueba\*.xlsx")
 Do While Archivos <> “”

 'Paso 3: Abrir los libros uno por uno
 Workbooks.Open "C:\Users\fernandofernandez\Desktop\Prueba\" & Archivos

'code:

  ***Workbooks("New Data.xlsx").Worksheets("Export").Range("A2:D9").Copy


  Workbooks("Reports.xlsm").Worksheets("Data").Range("A2:D9").PasteSpecial Paste:=xlPasteValues***


 'Paso 5: Cuadro de mensaje, cerrar y guardar cambios

 MsgBox ActiveWorkbook.Name

 ActiveWorkbook.Close SaveChanges:=True

'Paso 6: buscar más archivos en la carpeta para volver seguir la secuencia

 Archivos = Dir
 Loop

End Sub

Answer 1

There's few cases where I would every use copy PasteValues. Sometimes there's a case for formatting or formulas, but for values, I would always recommend using the approach of just setting the Value. See example:

Workbooks("Reports.xlsm").Worksheets("Data").Range("A2:D9").Value = _ Workbooks("New Data.xlsx").Worksheets("Export").Range("A2:D9").Value