Excel Formula to add network file link to each month

[D L]

Hi:
I have an excel 2007 file called YTD (Year to Date). It has formulas
that contain links to other excel workbooks on a shared drive. Here is
an example of the formula:

='Z:\PIG\Tony\By Market\2011\1-11\[Fa SS savings 1-11.xls]TC3'!C8+'Z:
\PIG\Tony\By Market\2011\2-11\[Fa SS savings 2-11.xls]TC3'!C8+'Z:\PIG
\Tony\ByMarket\2011\3-11\[Fa SS savings 3-11.xls]TC3'!C8

Each month I first complete the files referenced in the formula then I
go to this YTD file and I add the next month I have completed. So for
example, I have just completed all the 4-11 files, so i need to add:
+'Z:\PIG\Tony\ByMarket\2011\3-11\[Fa SS savings 4-11.xls]TC3'!C8 to
the formula above and i have a lot of tabs on 2 different worksheets
with multiple columns (22 columns on each tab) I need to do this on
and it is very time consuming. So starting in Cell C8 (column called
"# of lines") I need to add the new month's file name link to the end
of the formula and the same in cell D8 (column called "Savings").
These 2 columns and names will be the same across the 22 columns (# of
lines, Savings, # of lines, Savings, etc for 22 columns on each tab)

Is there a way I could do this using VBA and just run a macro each
month to add the month I'm working on. So in July I will be working on
May's file and will need to add the May file reference for example. I
have tried recording a macro, but since each tab has a different name,
I have to make a new macro that will run on each tab.



Thanks!
D

 

[Vacuum Sealed]

Hi D

I have something that may help you streamline your entire process.

I have a master file that backs up to a seperate historic file, the neat
thing is, it has tabs for each month (with a tab for Monthly Calculation
breakdowns and averages including an overall YTD tab already plotted which
you can mod to suit), the macro selects the appropriate Month/Tab and
inserts my specific range at the 1st available blank cell along my target
column.

I'm more than happy to send you the blank Master & History templates for you
to look at.

It is a Historical Tracker for transport freight movement, so you will have
to do some modding to suit, it shouldn't take you long, assuming you have
good to intermediate VB Knowledge.

If anything, it should give you an insight into how you could use the code
to restructure the way you do your current process so that the entire year
would be housed in the one file broken into every month.

HTH
Mick.