Sunday 10 March 2013

VBA Macro: How to add and rename worksheets

In following demonstration video I will implement Excel Visual Basic macro that reads worksheet names from the cells A1, A2, A3, ... in sheet "Sheet1" and creates new worksheets with correct names.

For simplicity there is no error handling etc and following simple code does the trick:


Sub createsheets()
    Dim newsheet As Worksheet
    Dim r As Integer
    r = 1
    Do While Sheets("Sheet1").Cells(r, 1).Value <> ""
        Set newsheet = Sheets.Add
        newsheet.Name = Sheets("Sheet1").Cells(r, 1).Value
        r = r + 1
    Loop
End Sub






Monday 4 March 2013

Excel VBA Macro: How to name Worksheets

You can have several worksheets in one Excel workbook. Default names of worksheets are Sheet 1, Sheet 2, ... You can change names manually by double clicking the name of the sheet, but you can also automatise this.

In the following demonstration video I will implement a simple VBA macro that names all worksheets in Excel workbook according to the content of the each worksheet's cell A1. You can go through all worksheet objects by using For Each -loop:


Sub namesheets()
    Dim s As Worksheet
    For Each s In Worksheets
        s.Name = s.Cells(1, 1).Value
    Next
End Sub