VLOOKUP Challenge

In this video, we’re going to look at how to copy and paste data below a table. So in this example here I have all of my data on one sheet, I have my table over here on the left and then my new data on the right. Of course you could add this new data on a different worksheet or even a different workbook, the technique is going to be the same, it just makes it easy for the example. So really, what we wanna do and as if we were to do this manually in Excel, we would copy this data, so select this range here, control C or right click copy and then when we paste it to the bottom of the table, even if we have the total row enabled, we can just select this first cell here below the data body range in the table and hit control V or right click paste, that will add new rows to the table, insert our pasted data here and then move the total row down as well. So it does all that work for us when we do the paste. So really, we need to write a macro to do the same thing. And fortunately, it’s very easy. So let’s jump into the VB editor here and we’ll take a look at this macro. It’s called paste below table. So the first thing we’re doing here is again setting reference to our list object and then here we have the copy range where we’re going to copy that specific range, so we’ll go ahead and step through this. I’m going to jump back into Excel, just going to undo what I just did so we can see how this works, again. So I’ll jump back over to VB editor, put this kind of side by side here and we’ll take a look at how this works. So we go ahead and step through this, hit F8 on the keyboard. So again, we’re gonna set reference to our list object, then we’re going to copy that range which is I5 to N13, so that would be this range right here. Jump back to VB editor, so hit F8 to copy that. Now this line of code is just going to find the number of rows that have been used in the data body range of the table, so we have databodyrange.rows.count, we can see [inaudible 00:02:00] that 11, that’s going to set this variable equal to 11. We actually don’t have to have this line of code, we could use this portion of the code in the line that we’re gonna look at below but it just makes it easier to have a variable here and to test that, make sure we’re getting the right number. So total number of rows used in our table is 11. Now this line of code does the paste and it’s going to find that last row in our data body range and it does that with this data body range properties reference here. Within that, we have perimeters, again, for the data body range, and those perimeters are the row index and the column index. So we’re specifying the row number here, this is going to be row 11 plus one, so really, this will be row 12 which is the row right below the last used row here in the table, row 12. Again, that’s gonna be the row number of the table, not the row number of the worksheet. So 12 rows down and we’ll just start in the first column. So the column index equals one, first column in the table. And we’ll run the paste special method right there. So we hit F8 right now, that’s going to do exactly that and if we jump over to Excel here, we can see our table has extended down. Total row still enabled here, moved down for us, the table’s been extended and everything looks good. And so really, that’s it, that’s our macro there to copy and paste below a table. Now this last line of code here will just turn off the copy mode and that’s those marching ants that we see around the copy range. So if we jump back to Excel here, we see that we still have this range being copied. You might wanna hit escape on the keyboard so the user doesn’t accidentally hit enter and paste this somewhere else. And do that in VBA, we just set the cut copy mode of the application equal to false. Now it’ll do the same thing as hitting escape and turning off those marching ants. We’ll hit F8 there, jump back over to Excel, see that’s no longer on the clipboard there and we’re good to go. And again, that code there is fully dynamic. So if we run it again, if we just run this macro again, it’s again going to paste those rows to the bottom of the table. Add rows to the table, paste them down there, move everything down and everything’s good. So the only thing you might need to do is if you have a sheet of raw data is determine the last used row on that sheet. So right now, we referenced this hard coded range here, I5 to M3 is our copy range, you might have to go find the last used row on that sheet and we covered that back in a previous module in the course. Now, one other thing we might wanna do is copy down the formatting as well ’cause sometimes our raw data is not formatted, something like this over here, we [inaudible 00:04:49] this date column, it’s not formatted as dates, it’s just a serial number for the date and then when we paste it below, we won’t necessarily get that new number formatting copied down from the table. Same thing with our numbers over here as well. So we can do that with our macro, I’ll just jump in here and we have another macro called paste below table formatting. And right down here, it’s really the same macro, just at the bottom of the macro, I’ve added two lines of code. This first one is going to copy the first list row here, so it’s referencing the first list row and then that range.copymethod, so it’s going to copy the entire first row in the table, and then paste it to the entire data body range of the table using the paste formats paste type. So use paste special command just pasting the formatting over the entire table. Now you could also just reference the new rows here if you wanted to do that, but sometimes it’s just easier to just copy and paste formatting over the entire table there to make sure all the cells in the table are formatted properly with those proper number formats. So running this here, if we just step through it now. Again, once we get to this line of code here, we’ve done the paste special, let’s jump over to Excel, we can see here that we have our new data in the table but again, the date formats here in this column are not correct and then same with the numbers over here. So jump back to VB editor now, again, I’ll just turn that copy mode off. Here, we’re going to copy that first range in the table, we can see that … I’m sorry, the first row, we can see that right here. Just copying that to the clipboard and then we’re going to do a paste special on the entire data body range with just the format, it’s just the formatting right here. So hit F8 again, that will paste the formatting over the entire table, now we can see our dates look good, move this over, our numbers look good as well. Then we might also wanna add a line down here, the same line or just move this line down for the copy method. Just move that to the bottom, it should have that at the bottom. So we’ll turn our copy method off after we’ve done all of our copying and pasting. So again, very simple way to just copy data below a table, another great use for a table here is a easy way to copy and paste new data or append new data to the bottom of the table. So in the next video, we’ll take a look at how to filter tables.