The BEST Way To Move Sheets Between Files In Excel (MM LITE #2)
Chris Mortimer
Chris Mortimer
Founder of Tiger Spreadsheet Solutions. I love bringing the powerful stuff in Excel to people like you. Fan of drumming, chess and flatcoat retrievers.
The BEST Way To Move Sheets Between Files In Excel (MM LITE #2)
Moving data between files is something people do in Excel all the time. But, it can be time-consuming and frustrating, particularly if it’s a task you have to do in Excel everyday or every week. So, what’s the best way to do it?
Excel offers multiple options from the manual ‘copy sheet’ operation to Power Query, VBA code and others. In this video, I demonstrate the basic ‘manual’ method, then set up and compare two ‘automated’ options using (1) VBA and (2) Power Query. Either can save you huge amounts of time; which suits you best depends on your particular Excel situation. So, let’s get into it!
TIMESTAMPS
00:00 Excel VBA vs. Power Query Introduction
00:26 Manual Sheet Copy Demonstration
01:06 Record VBA Code To Copy Sheet
03:41 What Is Power Query?
04:33 Import Data With Power Query Demonstration
06:46 Discussion: VBA Or Power Query?
07:56 Access An Exclusive MM 1-Hour Tutorial
By right-clicking on the sheet tab at the bottom of Excel, you can access the ‘move and copy’ dialogue box by selecting ‘Move or Copy’. I demonstrate this in the video with an additional challenge: I try to do it using keyboard shortcuts! I’ve always found this dialogue box fairly easy to understand and use – you can move sheets around a file, to another file, and have the option of copying a sheet before moving it, too, so it is duplicated in both locations.
So, what’s the problem? Well, even this manual process becomes onerous if you have to do it multiple times! Moreover, it’s prone to human error. There’s an additional consideration here too: do you actually need multiple files? And, might duplicating the worksheet like this cause problems later?
As with everything in Excel, the true answers lie at the ‘design’ level: the number of files you’re using (could you consolidate?), the number of sheets in each file, and the role of each. Seriously, thinking about overall design saves so much time and stress: you might not need a fancy Power Query data link or VBA routine at all …
Let’s assume we do, at least for now. If you’re using desktop Excel, the next option is to automate this sheet copy / move process using Excel VBA. What, you don’t know VBA code? No matter. One of the benefits of VBA is that code is easily generated using the macro recorder. This is what I do in the video: set the recorder going, do the actions, then stop the recorder and view the code. With a bit of experimentation, you may be able to ‘tweak’ the code to get it doing exactly what you need, without much prior knowledge of VBA. Computer code is language and it can be intuitive …
Many people, however, don’t want to delve into VBA. They don’t wish to get involved with computer code and perhaps they’re not using desktop Excel. I can understand. Power Query is a ‘powerful’ alternative that doesn’t require any coding. You do, however, have to navigate the Power Query interface which can be intimidating. It’s worth trying, however, because Power Query offers some useful options such as importing only filtered rows and even cleansing data before it comes into the file.
The downside of Power Query? It creates a ‘data link’ between the two files. Yes, that annoying ‘do you wish to update data links?’ notification when you open up Excel is now ‘in play’. And, you’ll have to get the data link to update which, I have found, is not always consistent. This, for me, is where VBA is advantageous because we can control the data transfer process at the click of a button without any annoying data links between the files.
If you’ve downloaded the files and worked along with me, you should now have a feel for the 3 data transfer options. The manual option works well but will soon frustrate. Whether VBA or Power Query suits you best depends on your circumstances. Are you on desktop Excel? And are you comfortable using some code? Perhaps you’re excited by the prospect of learning some VBA? If so, I would recommend the VBA option over Power Query. Let me know what you think in the YouTube comments.
Enjoy this type of Excel VBA tutorial? We do it twice monthly, live in our Members' Monday community. Click below to ask the FREE taster area and view sample sessions.