Put the ‘tell’ block inside the repeat instead of vice versa:
set franchise to {"Adelaide", "Airlie Beach", "Bayside", "Cairns", "Camden", "Canberra", "Central West", "East (Brisbane) & Central", "Fraser Coast", "Gladstone / Rockhampton", "Gold Coast", "Goondiwindi", "Gosford", "Hornsby", "Ipswich", "Logan", "Mackay", "Mid North Coast (Coffs Harbour)", "Mt Gambier", "Northern Rivers", "Northside", "Pine Rivers", "Redcliffe", "Southside", "Sunshine Coast", "Tamworth", "Toowoomba", "Townsville (Carpet)", "Tweed Coast", "Westside"}
tell application "Microsoft Excel" to open "Macintosh HD:Rob:Documents:Monthly_2010.xlsb"
repeat with i in franchise
tell application "Microsoft Excel"
-- open "Macintosh HD:Rob:Documents:Monthly_2010.xlsb"
set value of cell "T2" of sheet "Report" of document 1 to i
delay 1
print out sheet "Report"
set NewFileName to value of cell "T9" of sheet "Report" of document 1
end tell
tell application "Finder"
set theFolder to folder "Monthly Reports:Reports :Temp:" of home
set targetFolder to folder "Monthly Reports:Reports :AUG:" of home
delay 3
set name of first file of theFolder to NewFileName & ".pdf"
move (every file of theFolder whose name ends with ".pdf") to targetFolder
end tell
end repeat
Note (if it helps) that the Finder has its own home keyword. It also has one for the desktop, so if you’re going to be using tthe Finder, you could use the Finder terms instead of the path to command. (Incidentally, where you have in your problem script (path to desktop folder as text), the word ‘folder’ is superfluous. You have to use it with some of the path to destinations, but not with the desktop. It doesn’t actually do any harm. It’s just not necessary.)
The action in the Finder tell block is a little disturbing to read, but I don’t know what situation it’s addressing. It takes some file which the Finder happens to think of as the first one in the Folder, renames it with a “.pdf” extension, and then moves all such files out of the folder. Hopefully there are some files left behind to be renamed in the following iterations of the repeat.
I’ve no idea why not. If that’s the entire script and you’re doing exactly as you describe, it should work every time. I don’t think it’s a scripting problem.