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

Check out the video!

X

Forgot Password?

Join Us