Loop to open successive filenames...?

Hi

I have a speadsheet Analysis.xls which using VBA pulls in lines of totals from 12 other spreadsheets named Spending001.xls to Spending012.xls. This is a simple copy and paste exercise and the vba I am using is in the attached .txt file.

Rather than repeating the same code 12 times, please could you suggest a loop that would increment the filename (001-012) each time around? I do understand simple loops, but this is giving me problems.

Thanks for your advice
Robin

Something like this (albeit untested)

Sub test()

Dim WBS As Workbook
Dim str As String
Dim filename As String

Range("J5").Select

'
    
'
For i = 1 To 12
    str = Format(CStr(i), "000")
    filename = "D:\Spending" & str & ".xlsx"

    Set WBS = Workbooks.Open(filename)
    WBS.Activate
    Range("AJ372:CU372").Select
    Selection.Copy
    Windows(Analysis.xlsm).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.Offset(1, 0).Select

Next i


End Sub

Thanks, that's great and works well.

Please just one last question...

How could VBA import the end of loop figure of 12 as an integer from a cell in the spreadsheet, so I can change this figure by entering it in a cell rather than editing the VB code?

Thanks
Robin

For i = 1 to Range("A1").Value