F
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
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