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
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 theValue
. See example:Workbooks("Reports.xlsm").Worksheets("Data").Range("A2:D9").Value = _ Workbooks("New Data.xlsx").Worksheets("Export").Range("A2:D9").Value