Pasting values from one Excel Workbook to others

Question

I want to copy the same values from one workbook to 30 other workbooks.

I found a macro that open and copies all the values of the files of the first workbook.

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

Also I don't know how to 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

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

Try avoiding select and have a look at this if it helps, give it a vote : https://stackoverflow.com/q/50776026/4961700 — Solar Mike, Jun 01 '19 at 14:47
Oh, I misunderstood what was going on... Okay now I'll really give you click! — pgSystemTester, Jun 01 '19 at 15:10
@SolarMike I don't like any of the answers on there! I'll post one later. — pgSystemTester, Jun 01 '19 at 15:17
@PGCodeRider are they that bad? When you post yours, copy me in - I will learn something for sure :) cheers. Note I am not a programmer - as you can tell from my approach to code... — Solar Mike, Jun 01 '19 at 15:19

score 2 · Answer 1 · answered Jun 01 '19 at 15:05

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

Pasting values from one Excel Workbook to others

1 Answers1