Have Questions? 
Excel Ninja Pro
All Access

May 5, 2020

Create Sheets For Each User From Column A

Sometimes in Excel, we need to automate simple tasks, like to create sheets automatically for each item in a range.

If you just had a few worksheet tabs that needed to be created, it wouldn’t be a big deal, but what if you had to create 100 different worksheet tabs?!

Enter the magical world of Excel VBA!

Let’s begin!

Firstly, we need to Get the Last Row dynamically.

'grab the last row
lastRow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).Row

Now, we can dynamically go from row 2 to the lastRow variable easily in a loop.

For x = 2 to lastRow
     'do something here
Next x

Each iteration of x (starting at 2 and ending at whatever your last row is) needs to add a worksheet AND needs to be sure to rename it to whatever the current row (x) is on column A (or column 1).

Here’s the completed code, but be sure to check out the video if you need clarification!

Sub newSheetPerName()

lastRow = ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row

For x = 2 To lastRow
    Set curWs = ThisWorkbook.Sheets.Add(After:=Sheets(Sheets.Count))
    curWs.Name = ThisWorkbook.Sheets("sheet1").Cells(x, 1)
Next x

End Sub

Excel VBA Is Fun

We believe Excel is one of the most versatile programs out there - and learning to program and automate it makes you very valuable!

You can prevent wasted time and errors, speed up tasks and become indispensable to your organization. 

See how our trainees have gotten raises, promotions and bonuses using their Excel Automation skills while building real problem-solving programs in Excel!
Copyright © 2024 ExcelVbaIsFun

Forgot Password?

Join Us

    Your Cart
    Your cart is emptyReturn to Shop
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram