Macro for removing columns/rows, freezing panes etc?
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... :-p Joanna |
Macro for removing columns/rows, freezing panes etc?
See my response in Functions NG. Otto
wrote in message ... 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... :-p Joanna |
All times are GMT +1. The time now is 07:54 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com