TigerVQ #4 Excel VBA to Copy Data to Worksheets - Full VBA Tutorial

Link to download files:

Tiger Viewer Questions playlist

Excel VBA for Beginners playlist

Have you ever had to copy / paste data in Excel from one sheet to another? Perhaps you've had to copy / paste certain rows from a dataset to separate sheets. I am pretty sure you are not the only one who has found themselves frustrated in this situation. It takes SO much time! Surely there is a better way...

We can use Excel VBA to copy data to sheets in Excel. In the video tutorial, we take an example (sent in by a subscriber to the Tiger mailing list) from the world of horse racing. The workbook contains details of horses in races from the past few years; the user wishes to be able to copy the horses from each race to a separate sheet. Sounds simple enough, right? It's a typical example and I'm absolutely sure you will have tried to do something similar at some point in your working life - I know I certainly have...

... in fact, I tried to do it for this tutorial (see the beginning of the video!). I copy / pasted a few entries onto separate sheets and was genuinely shocked at how much time (not to mention, energy!) this relatively simple task used up, and I soon gave up! I started thinking about how to exploit Excel VBA to copy data to sheets; the result was an awesome VBA routine that executes in just a couple of seconds. Yes, this manual task that takes up hours of your time can be reduced to just seconds.

In the video, I walk you through the code, step-by-step, using the same systematic coding approach I use in my day-to-day work. Some of the 'usual suspects' from the Tiger videos are there - variables, a loop, conditional statements, use of the macro recorder. The code is quite complex but, with some conceptual understanding, you will be able to make sense of it, and hopefully apply it in your work. If this video is too advanced, work through our 'Excel VBA for Beginners' playlist on YouTube first.

Good luck and let us know how you get on.

Get in touch with Tiger using the platforms below!
For regular spreadsheet hints and tips and more on the #ExcelRevolution:
Also on Insta :-)

[BenLinfordUK]

I watched this last week, and I've been meaning to leave you a comment.
Watching over your VBA videos has been invaluable for me in understanding the key concepts and structure of using VBA code and it's going to make for some very wowed and happy colleagues over the next couple of months, I'm sure.

I just went over the final spreadsheet and for anybody that has default date formatting with '/' separators, you may get an error during the line within the loop when the sheet gets renamed to the date value that relates to the data in the sheet that you are pasting.
This is because '/' is a restricted character with regards to sheet names.

To get around this I swapped the following line of code;
ActiveSheet.Name =

With this one;
ActiveSheet.Name =

By swapping '.Value' with '.Text' the formatted version of the date appears to be passed when naming the sheet.

I also added these lines of code just after to make sure I didn't get instead of dates (where the column width wasn't wide enough by default).
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select

Looking forward to more videos (VBA ones in particular).

Do you have any experience with using CommandBars?
I'm particularly interested in adding items to the menu that appears when you right click on the row numbers as opposed to cells if you know anything about that?
I've managed to get some things working with the Cells CommandBar.

Might be a potential topic for a future video?

Keep up the great work.

[decom]

You’re the reason why my boss likes me, lol! Thanks for all your tutorials. You have helped me tremendously!!!

[jacobnicholas]

I am immensely grateful for all of the VBA videos you've put together, Chris. I've gone from a complete VBA novice to having a decent grasp on some of VBA's functionality and power largely thanks to you.

[pauldunwoodie]

You make any task look easy using simple steps, cheers Chris and very helpful.

[anxiousdog]

Just what I needed. Great video! Thanks.

[Darko]

Hi Chris,
Great presentation! I would really appreciate if you could write down the code for copying data from different worksheets to one summary sheet. You could use this file as an example? How would you tweak the code to collate the data from the different worksheets to the results sheet?
Best regards,
Darko

[hughchenz]

Hi Chris
I just finished watching your video. I think i now have an idea of i want to do. My question required copying and then transposing to another area or cell. This video gave me a hint on doing that may need to be tweaked though. I would appreciate if you can provide me with the transpose code. Your videos are very good. Keep it up.

[alexandrumarcel]

You should specify that you should filter ascended or descended the column you work with, because if someone filter this file by let s say column "G" you would not get the same results

[monitoring_evaluation]

Please I wrote the same code to change sheet names as you but every time I run it, I get this Run Time error '1004'. Please how Do I fix it?

[tsl]

After running the code, a prompt shows that the sheet name is invalid. It should be out of the slashes of the date format. What can I do to fix it?

[chrisjames]

I’m using Excel 2007...I know it’s old but does this code work on that version as I’m copying your code but i keep getting an error. It says "Object doesn't support this property or method". Any help would be appreciated as this is the coding im looking for to help with a project.

[eslieterry]

Hey i have a question on how to add together for TAT but ignore the text N/A and keep counting the days. Do you have any idea's?

[jgeerinckx]

Hey Chris, u are using a very strange way of referencing even more in this video then the "Convert Minutes to Decimals". U have the range ChrisCell which is part of a range on Sheet(1) and then u use this range to get the address to input as a new range on Sheet(1). For me it looks the same like using Sheet(1).Range(Sheet(1).Range("A2").Address).Offset(0, 1), u could just use ChrisCell.Value and ChrisCell.Offset(1, 0).Value.

[Darko]

Hi again Chris,


I have tried to write the vba code to consolidate the data from two worksheets into one single worksheet. I have also tried to use some of your previous code from your channel, but unfortunately, I have encountered some problems. I would appreciate if you could validate the code :) Data range a2:f19 is supposed to be copied from sheets 2 and 3 into sheet 1. I know that that I have mixed up the vba coding ;)
Best wishes,
Darko


Here is the code:


Option Explicit

Sub Collate_Data()

Dim ws As Worksheet

For Each ws In Worksheets
If ws.Name <> "Sheet1" Then

Range("a2:f19").Copy
Sheets("Sheet1").Activate

If Range("d5") = "" Then
Range("d5").PasteSpecial xlPasteValues
Else
Range("d5").End(xlDown). _
Offset(1, 0).PasteSpecial xlPasteValues
End If

End If

Application.CutCopyMode = False

Next ws

End Sub