8. Copy Visible Rows from Filtered Table

In this video, we’re going to look at how to copy and paste the visible rows of a filtered table. This example is great if you have a table like this, and maybe you just want to copy and paste rows that contain a specific value in a specific column. For example, maybe we just want to take the rows that contain the region East in this table here, and copy and paste those to another sheet. To do that, one very simple way is to just filter the table and then copy the visible rows. If we were to do this manually, we could just go and filter the table like this for the East rows, and then select all these rows here, copy those. We can hit control C to copy those. That will automatically copy the visible rows only in a filtered table. Then go over to another sheet and just paste those somewhere on another sheet. That’s how we would do that manually, and we’ll take a look at how we can do this process with VBA. I’ll go ahead and hit escape here and also clear our filter and we’ll take a look at a macro that does this process as well. We’ll jump into the VB Editor here and in our list objects module, I’ve added another macro here called filter copy paste visible rows. This takes the same approach that we saw in the last video to filter the table for the East region in the region column here, or field four. So all of that code down to this point is the same and we’re just going to filter for the East region, and then all we need to do is copy those rows in the data body range. That line of code is simple as something like this right here, which is going to reference the data body range and then copy it. So I’ll go ahead and run the code down to this line, hit control F8 on the keyboard to run it all the way to that line. And if we jump back over to Excel now, we’ll see that our table is filtered here for just the East region. Hit Alt F11. And then when we run this line of code now, hit F8 on the keyboard to run that line of code, if we jump back over to Excel, we’ll see that we have the marching ants around the sections here for just the visible rows. You can kind of see that right here. These marching ants between rows nine and 13 right here. So we’re just copying the visible rows there. We don’t have to worry about specifying the visible rows with the special cells method there. However, we can do that if you really want to make sure that you’re only copying the visible cells, or if you’re not using tables, you can use a special cell. So dot special cells is the method here. Hit tab into that, open the parentheses, and then there’s all these options for special cells… I’m sorry, there’s a parameter here, a type parameter with all these options, different types, and we want type visible right here, cell type visible. So that’s going to only reference those visible rows or visible columns, just all of the cells that are visible on the sheet. So then we do a dot copy method there. If you’re not using tables, you can use the special cells method to reference those visible cells. Like I said before, if you are using tables, this really isn’t necessary, because when we copy from tables, we’re only getting those visible rows anyways. So that’s the line of code there, and then below that we’ll just have a line of code to do the paste. So here’s the line of code and this is just going to reference cell A1 on this paste visible sheet and do a paste special for the pasting values. You could change this to whatever you want. If you want to do a full paste that includes the formatting or just a number formatting or something like that, you can change the paste type right here. This is just going to paste value. So I hit F8 there, jump back over to Excel and check that out. Go over to this paste visible sheet. And so here we have our data for only the rows for the East region. So that’s the macro there for copying and pasting the visible cells of a filtered table. I also have some training in the VIP section of the course that covers how to split a table like this for all of the unique values in the columns. So in this case here, if we wanted to do that copy and paste, but create a sheet for the East region, then create another sheet for the North region, and the West region that only contained the data for each of those regions, and only copy and paste those the rows for those regions, I have another training that goes over that in more detail, it just requires a loop and then finding the unique values here within the column. So I’ll put a link below this video to that training as well. It’s a little more advanced, but another great technique there for splitting up a table for specific items in a column.