Excel VBA - Many Questions!
First off I'm a complete VBA novice and working my way through Tom's book which is great so far but could do with advice on something I need to do now!
I extract several reports from an HR IT System every month for a variety of data to compile an offline staff directory - all Excel format. I currently put this together manually using several vlookups, sorts etc. the data range changes every month as staff join and leave so the master file is a moving target.
Is it possible using Excel VBA to compile the master file with data from four or five different Excel Workbooks, held within folders on a server, all with different amounts of data. If I can give an example - the base data file containing the list of employees for that month contains 2 fileds for telephone prefix and VPN - I concatenate these into a single field and then VLOOKUP the telephone extension into the next column - some staff do not have telephone extensions input yet (eg a new joiner). There are four of five similar actions in the process. I have recorded separate macros in different workbooks for the concatenate and VLOOKUPS but when the amount of data in the master sheet changes the data selection is fixed to the range when the original macro was recorded.
Apologies if this is covered in a section of Tom's book I have not go to yet, or if this ramble makes no sense at all! Any advice would as to whether my scenarios can be achieved using VBA be greatly appreciated.