Look Who it Was Random video

Excel - Copy Previously Filtered Worksheet: Episode 1644

2,233 views · Published 13 February 2013 · 3:50 · Indexed 26 September 2026

Channel: MrExcel.com · 2013 · Education

Watch on YouTube

Microsoft Excel Tutorial: Copying from Filtered Worksheets with an Excel Macro.

Welcome to another episode of the MrExcel podcast. In today's episode, we will be discussing how to copy from each filtered worksheet in Excel. This question was sent in by Leon, who is having trouble with his macro copying the headings from each worksheet. Let's take a look at the macro and see how we can solve this issue.

First, we will run the macro that Leon has set up. As you can see, the headings from each worksheet are being copied along with the data. Leon's question is if there is a way to not copy those headings. To solve this, we will switch back and forth between VBA code and use the immediate window at the bottom to see what is being selected.

By using the code "active cell dot current region dot offset, one row down, zero columns to the left dot select", we can make sure that everything is selected except for the headings. This will solve the issue of the headings being copied along with the data. You may be thinking that this will also copy the blank row, but that is okay as we will be overwriting it in the summary worksheet.

Let's take a closer look at the code we have written. We are using a for each loop to go through each worksheet in the active workbook, and if the worksheet name is not equal to "summary", we will figure out where the next row on the summary sheet will be. Then, we will start at A1 and use the current region offset function to select only the visible cells. This will be copied to the summary worksheet in the next available row.

With this simple change, we can now copy from each filtered worksheet without including the headings. Of course, we will need to manually add the headings to the first worksheet, or add one line of code to the macro. But for Leon's problem, this solution will work perfectly. Thank you, Leon, for sending in your question and thank you for tuning in to another episode of the MrExcel podcast. We'll see you next time for more Excel tips and tricks.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

Table of Contents:
(00:00) Copying from Filtered Worksheets
(00:11) Problem with Macro Copying Headings
(00:27) Solution: Using Offset Function
(01:20) Explanation of Code
(02:12) Goal of the Macro
(03:10) Successful Execution of Macro
(03:40) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

This video answers these common search terms:
Adding one row offset in Excel macro
Copying records from multiple worksheets to a summary sheet
Copying visible cells only in Excel macro
Excel macro to copy data without headings
Excel macro to copy previously filtered records from each worksheet
How to prevent copying headings in Excel macro
MrExcel podcast episode 1644: copy from each filtered worksheet
Offset function in VBA
Solving problem with copying headings in Excel macro
Tips for using Offset function in VBA for data manipulation
Using Offset function in VBA to select data range
VBA code to select only data in Excel

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153466/

Leon runs a Macro that copies previously filtered records, per Worksheet, in his Workbook. This works great, but it presents a bit of a problem. The issue is that the Headings are also being copied - Leon does not need the headings each time the Macro runs and the data is copied. Follow along with Bill 'MrExcel' Jelen today, in Episode #1644 as he discerns the issue, institutes the necessary change and produces those copies without the Headings each time.

Learn More about VBA/Macros [Visual Basic for Applications] and how it can really ease your work-day! Look for Bill's book, "VBA and Macros: Microsoft Excel 2010" by Bill Jelen and Tracy Syrstad. The fastest, best way to go beyond the Macro Recorder and move up the Excel VBA learning curve...Includes crucial information on making Excel 2010 VBA code work with older versions. http://www.mrexcel.com/2010Books/vbamac2010.html 

...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! http://www.mrexcel.com/learn2010/LE2010.html 

"The Learn Excel from MrExcel Podcast Series"

Visit us: MrExcel.com for all of your Microsoft Excel Needs!

More from this channel