MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.

Posted by Mark O'Brien on January 25, 2002 7:57 AM

So this didn't help then?