Wise Owl Answers - How do I copy data without using copy and paste in Excel VBA?

Copying and pasting data is the standard way to move information between cells in Excel but did you know that you can transfer data without using copy and paste in Excel VBA? This video explains how to perform this feat by assigning the values of a range to another range. As a bonus, the video shows you how to loop through the worksheets collection, copying the data from each one to build a master list.

Chapters
00:00 The Question
01:10 Copying to a Destination
02:54 Copy and PasteSpecial
03:50 Assigning a Range to another Range
04:43 Assigning a Block of Cells to Another Block
05:53 Setting the Size of the Destination Range
06:53 Looping Through the Worksheets Collection
08:41 Referring to the Source Range
12:10 Techniques for Building a List
15:00 Referring to the Target Range
18:20 Assigning the Source Range to the Target Range
19:23 Getting the Column Headings
22:14 Tidying the Result

[jmathew]

Thank you for another very helpful tutorial.

[vs_gaming]

Andrew, thank YOU so much for sharing this approach! As for me, you and Leila Gharani are the two best excel teachers.

[KhalilYasser]

Amazing tips and tricks. Thank you very much.

[philipboakes]

As a VBA beginner, this type of tutorial (explained in English) is priceless.

[AmitSharma-pozb]

One of the most humble instructor /mentor/teacher ...One should have blind trust on your teaching skills and codes..Kudos to you Andrew..

[internationalpolitics]

Hi Andrew I learnt a lot from your videos and your way of explaining is best in the whole world.thanks for uploading VBA tutorials

[tejamarneni]

Hi Andrew. Thank you so much for the amazing videos. I am really glad to see new videos from you every day.

[krn]

Thanks Andrew. Another favorite to store for later use.

[benjild]

Been enjoying your “Ask a question” series of videos. Although a bit off topic, is there any chance you could do a video on applying time zones identifiers e.g. “GMT” to a date and time in a spreadsheet? I.e. derive current time zone identifier depending on where in the world the user is running the macro.

[michaelbaverstock]

Super duper! You inspire me to improve my VBA skills. I have a sub procedure that loops through a range and sends different attachments to various people but through each iteration of the loop I have to dismiss the outlook security warning. Can you offer a solution or ideally showcase this in one of your extremely helpful videos?

[abdiag]

Thanks a bunch for this mate. A quick one. In case the values we are copying from source range to target range is copied in another format e.g for dates how do you format the date columns(the entire range) into your own format. I was coping data pulled from an accounting system with this and I run into such a problem. Would appreciate your reply on this BIG BIG time bruh

[anjanav.j.]

Can you please do a video to create two pivot tables from different worksheets and connect those with a single slicer. also to create relationship between that two pivot tables before creating slicer using VBA .thanks in advance.

[sidbell]

Hi Andy - Lovely clear video as usual. (You know there's a "but" coming don't you? :-) )
According to Stack Overflow, it is better (and faster) to use .Value2 rather than .Value - I've been using this for a couple of years now. Can't say I've noticed much difference, do you have any particular thoughts on this?

[juanpablohorn]

Hi Andrew! As always, great video! I have a question. I've heard before that the most efficient way to copy information from one place to another in excel is using Advanced Filters. Is this true? If so, it would be interesting to know how to do that to bring information from other workbooks. I'm curious to know what to do with the criteria on an advanced filter when I want to extract the whole information of a worksheet using Advanced filters.

[amyf]

Resize should be a method, not a property! It's a verb! I'm having trouble conceptualizing that. It probably doesn't help that I'm trying to resize the print area of a worksheet because contrary to basic VBA principles the RANGE of cells that make up the print area is not a range, it's a string!! 🤯 So maybe you can do a quick video on how to work with the print area (setting, resizing, changing, etc.) as a string.