Hi.
I am using a workbook with quite a few different tabs for each individual supplier.
Each period end i have to copy a tab and create a workbook with the supplier name which has ONLY one tab.

Say i have 30 suppliers in Workbook one each in the individual tab. I used to right-click the tab, "move or copy", "new book" tick copy, "ok", and just save the new file. THen i'd have 30 new files that i sent out as a statement. Problem is that i have more than 30 of those :) but even with that quantity its quite painful to do every 4 weeks.

I tried to record a macro, but the directory where the file is saved is changing according to the period we're in, e.g. 1 to 13.

I tried to record a macro to do that, but it would save the files in the same bloody directory (pd 4 for example it would save in pd 5 as its where the macro's been created).
The filename stays always the same, the directory changes in range from:
c:dirPD1statement.xls
c:dirPD2statement.xls
...
c:dirPD13statement.xls

I need to have some sort of a lookup that fetches the Period number (it is on every sheet in the workbook), and inputs it in the file save as directory.
Or if you could think of any better way of doing it, then do tell.

Let me know if you need some more info.

Kind regards,
Daniel

Um... the filename should also vary, could also be like the name of the tab or smthn.


Thx
d