We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Macro that opens sequentially named workbooks and runs the same routine on each one

John Carney
John Carney asked
on
Medium Priority
356 Views
Last Modified: 2012-05-11
Basically, the folder  "H:\...\SourceFiles\" contains an expanding list of files with names like:

2011 Week 01.xls
2011 Week 02.xls, etc.

I want to open them one at a time and run the routine below on them. How do I do that? There might be other files in the folder that don't end with "Week ##.xls", so it would be important to have the file selection based on the common ending for each file.

Thanks,
John
Sub Aggregate()
Application.ScreenUpdating = False

Workbooks.Open Filename:= _
 "H:\Depts\css\A_ILS & Reliability\Reliability\1-CURRENT\System Reporting\SDAE\JAL\3_Working Files\SourceFiles\JAL SourceFile 2011 Week 01.xls"
 Dim top As Range, btm As Range, rng As Range
 Set top = [A6]
 Set btm = top.End(xlDown)
 Set rng = Range(top, btm).EntireRow

 rng.Copy
 ThisWorkbook.Activate
 Dim top2 As Range
 Set top2 = [A4].End(xlDown).Offset(1, 0)
 top2.PasteSpecial Paste:=xlPasteValues
 Application.CutCopyMode = False
 ActiveWindow.ActivateNext
 ActiveWorkbook.Close
 
Application.ScreenUpdating = True
End Sub

Open in new window

Comment
Watch Question

Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Just a quick check - did you need to open them in order? (i.e Week 01 first, then Week 02 etc)
John CarneyReliability Business Tools Analyst II

Author

Commented:
Hi Sid and Rory, thanks for posting. I can try this out at work tomorrow. I don't think the weeks have to be in order, I'm not sure.

- John
The code I gave doesn't open the file in sequential order. As far as I could understand from your query, it's ok if the files are opened in any order?

Sid
John CarneyReliability Business Tools Analyst II

Author

Commented:
Thanks, Sid. Sorry for the delay.

- John
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.