The Ultimate Guide to Copying Data using Excel VBA

The Ultimate Guide to Copying Data using Excel VBA

In this video I'm going to cover everything you need to know about copying data using VBA. We're going to look at:
* What to avoid
* How to ensure your code runs fast
* How to get the correct worksheet every time
* An in-depth look at the 4 methods
* The pros and cons of each method

#excelvba #excelvbacopy #ExcelVBAArray #ExcelVBAAdvancedFilter #ExcelVBAADO


Useful VBA Shortcut Keys
========================

Debugging:
Compile the code: Alt + D + L OR Alt + D + Enter
Run the code from the current sub: F5
Step into the code line by line: F8
Add a breakpoint to pause the code: F9(or click left margin)

Windows:
View the Immediate Window: Ctrl + G
View the Watch Window: Alt + V + H
View the Properties Window: F4
Switch between Excel and the VBA Editor: Alt + F11
View the Project Explorer Window: Ctrl + R

Writing Code:
Search keyword under cursor: Ctrl + F3
Search the word last searched for: F3
Auto complete word: Ctrl + Space
Get the definition of the item under the cursor: Shift + F2
Go to the last cursor position: Ctrl + Shift + F2
Get the current region on a worksheet: Ctrl + Shift + 8(or Ctrl + *)
To move lines of code to the right(Indent): Tab
To move lines of code to the left(Outdent): Shift + Tab
Delete a Line: Ctrl + Y(note: this clears the clipboard)

Table of Contents:

00:00 - Introduction
00:26 - Don't do this
01:06 - Speeding up your code
02:16 - Get the correct worksheet every time
02:31 - How to get the correct worksheet
05:45 - How to get the correct data range
07:11 - Method 1: For Loop & Range
12:38 - Method 2: For Loop - Arrays
16:26 - Method 3: Advanced Filter
26:40 - Method 4: ADO

[vpires]

The amount of knowledge shared in this vídeo for free is insane! It is sad I can only press like once!!

[tomharrington]

I have to watch these videos in 10 minute segments. There is so much valuable information I can't take it all in. I can see direct application for all of it.

[Excelmacromastery]

I hope you enjoy my latest video. Make sure to download the source code from the link in the description and let me know in the comments which part you found most interesting.

[albireocygnus]

I'm always impressed with your crisp clear explanations and always can learn something new. Simply excellent!

[jimfitch]

Your best tutorial yet on this topic! Thanks!

[rmscrisostomo]

You're the best, Phill Collins.
kidding, I'm really a fan of your work.

[reng]

I've been following you since long time ago, and all your videos have a great and professional content !!! I can say compare to others VBA youtubers you are the best to know more each of the concepts applied in VBA with clear examples. my hat off to you amigo!!!!

[adrisanme]

Thank you very much for the extremely powerful code

[mdbm]

Thanks. my colleagues and I did it ineffectively, but now I know how to do it correctly.

[freeliberalminds]

Wow! Awesome tutorial. Thanks a lot for this video tutorial for free sir Paul.Very Helpful tips from You.

[frikduplessis]

Paul thank you for a brilliant tutorial

[armotxa]

Thank you Paul. Your codes are just lovely 👍 fast. I use them often. 👌

[rsnowdozer]

I didn't know about the ”ADO" method. It looks pretty simple. That's cool. 👍

[adrisanme]

Extremely useful powerful code, thank you!!!

[joaocustodio]

Great as always. Thanks vert much, Paul.

[Suissair]

Everybody learning vba should see this video.. Thank you very much!!
I would like to add the filter results to a listbox and then sort my Lb(column 'x') (a-z) ?

[francoismarsau]

Great video. I learn more every day from this channel. In this video you mentioned that the down side to the advanced filter is it gets slow the more frequently you use it. Why is this? Is there a solution? Please assist as I love using advanced filters.

[thesilverkey]

John, thank you. Where does the FROM Data$ bit come from? it's the sheet name. Nevermind. <Table> is sheet.name and <Field(s)> are are the Row1 Headers. Got it. Thanks again!

[michaelsvenson]

I´m not a programmer in any way, just doing some stuff for my archeryclub, but this thing you do is amazing. I will try to use some of your code if I can make it to run as I like. Great videos.... 🙂

[bigE]

I’ve learned so much from your videos!! One question… how would I use ADO to update existing records in another workbook and add new records from data in the active workbook in one query?