Macro for removing specific rows and columns, freezing panes..?

[fibbling]

Hi all,

I have only been using Excel for about 4 months and in the process of
learning new things. I want to make use of macros to automate the
repetitive tasks I do every day, but just when I think I've mastered
each one, I get error messages.

To make myself clearer: I work in a call center, and every day I have
to sift through about 8 *.xls files containing raw data to gain
figures about call volumes. I then copy and paste the relevant
figures into a properly formatted Master worksheet with formulas etc.
The tasks I do every day are repetitive, but I don't know whether
these types of tasks can be automated, can someone please advise?

1. Filtering: Deleting the same columns and freezing panes at the
exact same cell.
On most of the raw data sheets, there is a lot of unnecessary data,
and so before I even start to examine it, I eliminate the excess data
by removing the same irrelevant columns, and then I finally freeze the
pane at the same cell.
I tried to record a macro of me doing this, but I get an error message
telling me:
Run-time error '1004' Cannot use that command on Overlapping
Sections

...yet i get no error message when performing the exact same thing
manually. Surely there must be a way to automate this...?

2. Remove most rows: In certain raw data sheets, I face a mountain
of figures yet I only need to get 4 figures found on 6 rows. The
other 300 or so rows are unnecessary, and so I have to manually scroll
down through them all until I see the row I need. The rows I need are
easily identifiable, as they are the only rows that contain words
("Brazil Total Call Volume", "Ireland Total Call Volume" etc). Can I
set up a filter or rule to display only what I need?

3. Finally (and I'm guessing this would be too good to be true..) -
is there any function or even 3rd-party program that can actually
batch-extract the data from predefined cells on a worksheet and paste
them into pre-defined cells on another worksheet? Then I really would
not have to work at all...

Joanna

 

[Otto Moehrbach]

Joanna

What you say about what you have and what you want appears to be
tasks that are very easy to do in Excel. The devil, of course, is in the
details.

Some details: You say that you start with 8 Excel files. Is it
ALWAYS 8 files or is that number variable? Excel must be able to find those
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? Is the Master you refer to a file or a sheet in some file.

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

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

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

Otto

 

[fibbling]

Hi Otto,

Thanks for replying! To answer your questions:

Always 8 files

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

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..?)


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.

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.


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

 

[Otto Moehrbach]

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 (e-mail address removed).
Remove "extra" from this address. Otto