To program that in VBA you'd need to keep track of where the data ends in a variable, incrementing it as you go along, and using .Special Cells(xl Last Cell) if you wanted to resume the process later.Hi I'm New - You can move or copy the sheet into the master file by 'right click' the worsheet tab at the bottom and then selecting 'move or copy'. If you really meant "workbook" then you could program it in VBA, building around this statement, depending on Workbook names and sheet names Sheets("Sheet1"). Sheets(1) You can use the Dir command to cycle through your "input" workbooks.
Make sure to select extra rows after the data to make room for the data you're adding.You will see the Reference field populate with the spreadsheet information (it will look something like this: '[DS Excel Book 1.xlsx]Sheet1'! In the Consolidate menu, under Use Labels, click to select "Top Row" or "Left Column," or both depending on where your labels are.For Function, choose "Sum" (this is for basic consolidation, see the link in Resources for more complex tasks). Then select one of the Excel spreadsheets you want to merge.Click the upper left cell on the spreadsheet and drag to select the area to merge.This is known as merging spreadsheets, and it's a fast way to combine data without the hassles of copying, pasting and formatting.
These instructions are for Excel 2013, but the process is similar in earlier versions of the program.
It takes all the files, puts each into its own tab on a workbook, then can consolidate it further line by line into a single sheet.
Because this is all I need it for, it seems silly to buy it (not a fortune, about 40 dollars for a year) and I won't have to use it that often.
I found this excel add-in on the microsoft site and downloaded it for a 15 day trial.
It actually does alot more than I need, but handles this particular task perfectly.
I was wondering how to tabulate large amount of information gathered through surveys.