Want to do some coding? Me too of course you.. do it is #macromondays after after all. Today let’s look at a creating a tiny Excel Macro to remove a lot of work.
I always like to share my time wins with you and this is definitely one of them if you need to repeat your column headers on a tonne of Excel worksheets. There is alternatives ways to do it with the Menu options in the Excel ribbon but hey, that is
- not as much fun and
- not as fast as this!
What Does The Macro Do?
This macro will take a source sheet (the one that contains the row columns headers) and paste them into the first row of all of the other worksheets
How Does It Work?
FIRST. Open Visual Basic – by hitting ALT +F11 or Developer Tab . You can store this Macro in the current workbook, or if you wanted to re use the code you can store it in your Personal Macro workbook, but you will need to re work the code to specify your range you want to fill across other worksheets. (In this example our range to fill is contained in ‘sheet1’ of the workbook.
Step 1. We need to declare a variable, which simply creates a memory container in Excel to store our range from ‘sheet1’
Step 2. Excel sets the range Worksheet 1 of the active worksheet
Step 3. Excel then fills the specified range of rows 1:1 ( row 1) from the active workbook sheet1 to all other worksheets in the active workbook.
Step 4. Test Your Macro!
You can now pre-fill your headers with the click of a mouse!.
Want To Copy The Code?
[stextbox id=”grey”]
‘macro by howtoexcelatexcel.com
Sub CopyMyHeaders()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(“sheet1”)
Sheets.FillAcrossSheets ws.Range(“1:1”)
End Sub[/stextbox]
If you want to see all of my blog posts in the Macro Mondays Series you can find them all in the link below. Why not book mark it?, Yes it is updated EVERY Monday.
You can also download the corresponding example workbook that contains the Macro code by clicking on the download button below.
How To Excel At Excel – Macro Mondays Blog Posts.
[wpdm_package id=’10338′]
