The Problem (Q-score 5, ranked #257th of 303 in the Excel VBA archive)
The scenario as originally posted in 2009
I monthly receive 100+ excel spreadsheet from wich i take a fixed range and paste in other spreadsheet to make a report.
Im trying to write a vba script to iterate my excel files and copy the range in one spreadsheet, but i havent been able to do it.
Is there an easy way to do this?
Why this Range / Worksheet targeting trips people up
The question centers on reaching a specific cell, range, or workbook object. In Excel VBA, this is the #1 source of failures after activation events: every property (.Value, .Formula, .Address) behaves differently depending on whether the parent Workbook is explicit or implicit.
The Verified Solution — niche answer (below median) (+6)
16-line Excel VBA pattern (copy-ready)
Here’s some VBA code that demonstrates iterating over a bunch of Excel files in a directory and opening each one:
Dim sourcePath As String
Dim curFile As String
Dim curWB As Excel.Workbook
Dim destWB As Excel.Workbook
Set destWB = ActiveWorkbook
sourcePath = "C:files"
curFile = Dir(sourcePath & "*.xls")
While curFile <> ""
Set curWB = Workbooks.Open(sourcePath & "" & curFile)
curWB.Close
curFile = Dir()
Wend
Hopefully that’ll be a good enough starting point for you to work your existing macro code.
Loop-performance notes specific to this pattern
The loop in the answer iterates in process. On a 2026 Office build, setting Application.ScreenUpdating = False and Application.Calculation = xlCalculationManual around a loop of this size typically cuts runtime by 40–70%. Re-enable both in the Exit handler.
When to Use It — vintage (14+ years old, pre-2013)
Ranked #257th in its category — specialized fit
This pattern sits in the 98% tail relative to the top answer. Reach for it when your scenario closely matches the question title; otherwise browse the Excel VBA archive for a higher-consensus alternative.
What changed between 2009 and 2026
The answer is 17 years old. The Excel VBA object model has been stable across Office 2013, 2016, 2019, 2021, 365, and 2024/2026 LTSC, so the pattern still compiles. Changes that might affect you: 64-bit API declarations (use PtrSafe), blocked macros in downloaded files (Mark-of-the-Web), and the shift toward Office Scripts for web-first workflows.