Today’s problem: I had a large collection of Excel worksheets (around 200 or so) that were all in separate files and I wanted to merge them all together into one master file. (To be clear, I needed the resulting file to contain all of the 200 worksheets in order — not have one big worksheet with all of the data in one worksheet.) I couldn’t find a quick way to merge them all at once (without purchasing software), so I decided to try to make a macro that would let me move them one by one into the master file. I don’t really have a lot of experience making macros, so I spent the better part of an hour trying to figure out how to do it.
Here is what I came up with. This macro will let you move the current sheet to the beginning of the collection of worksheets in a file called Master.xls. (Before you try to use this macro, you should have already made a file called Master.xls.)
‘ MoveWorksheet Macro
‘ Move a worksheet to another file.
‘ Keyboard Shortcut: Ctrl+j
ActiveSheet.Move Before:=Workbooks( _
Perhaps somebody who reads this could tell me how I would need to modify the macro to have it move the file to the end of the collection of worksheets in Master.xls instead of the beginning. Or how I could have just merged all of the the worksheets into one file without having to open each one and press Ctrl+j. Suggestions welcome!