» Copy cells to all workbooks in a folder using VBA in Microsoft Excel
VBA macro tip contributed by Ron de Bruin, Microsoft MVP - Excel
CATEGORY - Cells, Ranges, Rows, and Columns in VBA
VERSION - All Microsoft Excel Versions
You can change the range and the sheet name as you like.
There is a macro that does a normal copy and one that is only copy the values.
Sub TestFile()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim i As Long
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "C:\Data"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
Set basebook = ThisWorkbook
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles(i))
Set sourceRange = basebook.Worksheets(1).Range("a1:c5")
Set destrange = mybook.Worksheets(1).Range("a1")
sourceRange.Copy destrange
mybook.Close True
Next i
End If
End With
Application.ScreenUpdating = True
End Sub
Book Store:
No comments have been submitted.

