Consolidate spreadsheets in different files into one spreadsheet
Posted by Ron on January 25, 2002 7:09 AM
I have several Excel files that I need to consolidate their individual spreadsheets into one spreadsheet each week. The individual spreadsheets (files) vary in length depending on how much information the user's input each week. Today, I select the range to copy from each individual spreadsheet (in files 1,2,3, etc) and copy them to a consolidated spreadsheet. I would like to run a Macro that automatically selects the 'used' range (rows 3 to the last used row that is used) & columns (A to M) and excludes the header rows (1 & 2). As an example, I want to highlight range A3:M43 and copy/paste it to a spreadsheet. Then take a another separate spreadsheet (file) and highlight A3:M26 and copy/paste to the consolidated spreadsheet. I do this for 20 files. Does anyone have a Macro or VB code that can do this? The ActiveSheet.UsedRange code highlights the whole spreadsheet. Would appreciate any suggestions you might have. Many thanks.