macro to copy and name a sheet

[Fraserkitchell]

I was hoping to get some help with a macro. I need to copy a master sheet and create a number of new sheets equal to the number of items in a list.

Thus, if I have five buildings

Building A
Building B
Building C
Building D
Building E

The macro would create five sheets, named "Building A", "Building B", etc. Each sheet would be the same as a master sheet.

I think this is doable...

Thanks,

Fraser

 

[VoG]

Where is the list - sheet name and range?

 

[Fraserkitchell]

Sheet Name: Meter and Buildings
Range: B5:B400

Thanks for looking!

 

[VoG]

Try

Sub Master()
Dim LR As Long, i As Long, j As Long
With Sheets("Meter and Buildings")
    LR = Range("B" & Rows.Count).End(xlUp).Row
    For i = 5 To LR
        .Copy after:=Sheets.Count
        ActiveSheet.Name = .Range("B" & i).Value
    Next i
End With
End Sub

 

[Fraserkitchell]

Thank you very much. This is not quite working for me yet.

the macro stops aT

.Copy after:=Sheets.Count

with the error message

run time error '1004':

Copy method of Worksheet class failed

Any ideas?

 

[VoG]

Sorry, it should be

Sub Master()
Dim LR As Long, i As Long, j As Long
With Sheets("Meter and Buildings")
    LR = .Range("B" & Rows.Count).End(xlUp).Row
    For i = 5 To LR
        .Copy after:=Sheets(Sheets.Count)
        ActiveSheet.Name = .Range("B" & i).Value
    Next i
End With
End Sub

 

[Fraserkitchell]

Nailed it! Thanks VoG!