Joanne

Regarding the path to the files. The number of files is not
important. What is important about the folder that holds those files is if
that folder has any other files besides the ones you want the code to work
with. It's OK to have other files in that folder. The code just needs to
know how to identify those other files so it knows to disregard them.

Because the folder name changes day to day and you won't
necessarily be working the code on the same day as the folder name, a little
code like this below can be used to get the path. This little code will
display an input box and ask you to enter the folder name. That would be
the "6th March" folder only. Would this work for you?

Sub GetPath()

Const FixedPath = "C:\Documents and Settings\Joanna\My Documents\Daily
Reports\"

Dim ThePath As String

ThePath = InputBox("Enter the folder name.")

ThePath = FixedPath & ThePath & "\"

MsgBox ThePath

End Sub

Regarding the Master file. The question here is where to place
the code (the macros). You could place it in the Master file. But if you
start a new Master file every month, you would have to copy the code from
the previous Master file into the new one. Another way is to place the code
in your Personal.xls file. That is a hidden file that is automatically
opened by Excel whenever Excel is opened. With the code in the Personal.xls
file, the code would be available to any open file.

In your explanation of what columns to delete and what cell to
freeze panes, you say that it differs for each of the 8 reports. That's OK.
Just tell me what and where for each report and I'll write the code that
way. That's not a problem.

Copying and pasting. Again, just tell me what cells to copy
from what report and where to paste them and I'll write the code to do that.

I think this project has moved beyond the scope of the
newsgroup. If you wish, send me instructions and sample files via email and
we'll work it that way. If you feel your data is proprietary, just fake
your data. I need only the layout of what you have to work with and
instructions of what goes where. My email is .
Remove "extra" from this address. Otto



wrote in message
...
Hi Otto,

Thanks for replying! To answer your questions:

You say that you start with 8 Excel files. Is it ALWAYS 8 files or is
that number variable?

Always 8 files

Is the path to those files always the same? Are other files located in
that same path? If so, how can the pertinent files be distinguished
from the rest?

Not at the moment, but they could be if necessary.
To explain better: every day I receive 8 *.xls files that provide info
for that particular day. The file names are *always* the same:
"Rep1.xls, Rep2.xls.. etc"
For backup/archive reasons, each day I create a separate folder with
the day's date, in which are stored these original 8 worksheets. Here
are some random examples that display the path:

C:\Documents and Settings\Joanna\My Documents\Daily Reports\6th Marchl
\Rep1.xls
C:\Documents and Settings\Joanna\My Documents\Daily Reports\6th Marchl
\Rep2.xls
C:\Documents and Settings\Joanna\My Documents\Daily Reports\7th Mar
\Rep7.xls

Is the Master you refer to a file or a sheet in some file.

When I say 'Master' I mean simply another *.xls sheet. In my specific
case, I enter the required figures daily into a speadsheet which
eventually becomes a monthly report. There are 3 'pages' or panes
within this worksheet (if that has any impact..?)


Deleting columns and freezing panes - easy. Of course, you have to say
what columns and what cell.

In each of the 8 reports the removable columns and freezing point are
always in the same place. I can manually remove the intended columns
by selecting each column (using CTRL to make individual selections as
opposed to mass selecting a single block) and then simply 'Delete'. I
then click on a cell (which is also consistent) to create the freeze
pane 'point'. Since I am doing this manually, I have to follow my own
list of instructions for each report, which looks something this:

Report 3 - Remove columns R-T, X-Z; freeze cell C14
Report 6 - Remove columns Q-S, V-Y freeze row D10
etc...

But whenever I follow these exact same steps while recording a macro,
I get that 'overlap' error message.

You say the pertinent rows have words. Do you mean the other rows have
numbers every time? In what column?

Technically speaking, the pertinent rows are not the only ones with
words, because there are also many rows with 'standard' words like
'Summary', 'Name'. But from a 'rules' point of view they are easy to
distinguish, as they have certain unique words in common that all the
unnecessary rows do not (e.g. "Final call volume total for...")
The reason I mention rules is because in these particular reports (2
of the 8 are like this), the data is not consistently located in the
same cell or row - only the column stays the same. Therefore, I think
this means I need to find a way to extract data based on format or
content rather than cell location.


Batch-extract data - easy. Of course you have to say from where to
where

I would like to be able to automate all the copy & paste actions and
switching back and forth between worksheets. For example, to instruct
Excel to copy the data in cells B5, B8, F6 and F13 on Rep02.xls, to
cells C2, H4, J2 and M7 on Stats.xls respectively. This would be a
godsend.. I spend well over an hour copying and pasting this kind of
data manually!


Joanna