Worksheet Macro

[mahen2]

I have two columns in sheet1. One is sheet Names and in column two Id numbers. I need to create worksheet with those names and enter appropriate Id number on to a cell (eg, in cell C3)

Sheet Names Id Numbers
Name1 A001
Name3 R012
Name7 A009
........ ........

i need macro to perform this please.

thanks

Mahen

[AB33]

Mahen,
Welcome to the forum!
Please change the name of your title which resembles your request.
Something like: Create sheet names(tabs) based on column A, or names.
It would make life easier if you could attach sample book with out any confidential information. To attach a sample, click advance then attachment

[TMS]

Sub TMS()
Dim lLR As Long
Dim cell As Range
Dim sh As Worksheet
With Sheets("Sheet1")
    lLR = .Range("A" & .Rows.Count).End(xlUp).Row
    For Each cell In .Range(.Cells(2, 1), .Cells(lLR, 1))
        Set sh = Sheets.Add(after:=Sheets(Sheets.Count))
        sh.Name = cell.Value
        sh.Range("A1") = cell.Offset(0, 1).Value
    Next 'cell
End With
End Sub

Regards, TMS

[TMS]

@AB33: apologies for responding whilst you have a rule compliance request in progress

[AB33]

Trevor,
You always are polite!

[mahen2]

See attached file. Click on sheets and check in Cell C3. I have about fifty Names and Ids in sheet1

Thanks
Mahen

[TMS]

Option Explicit

Sub TMS2()
Dim lLR As Long
Dim cell As Range
Dim sh As Worksheet
With Sheets("Sheet1")
    lLR = .Range("B" & .Rows.Count).End(xlUp).Row
    For Each cell In .Range(.Cells(3, 2), .Cells(lLR, 2))
        Set sh = Sheets.Add(after:=Sheets(Sheets.Count))
        On Error Resume Next
        sh.Name = cell.Value
        On Error GoTo 0
        sh.Range("C3") = cell.Offset(0, 1).Value
    Next 'cell
End With
End Sub

Regards, TMS

[AB33]

As an option

[mahen2]

TMShucks,
Thank you. Working fine. Is it possible to Create a copy of a sheet and rename with the names in Sheet1?

Mahen

[mahen2]

AB33,
Not quite right. Worksheet names has to be Name1, Name3....etc. Id numbers are placing correctly.

[AB33]

Sorry! You are right!
Corrected

[TMS]

Of course. Something like:

Option Explicit

Sub TMS3()
Dim lLR As Long
Dim cell As Range
Dim sh As Worksheet
With Sheets("Sheet1")
    lLR = .Range("B" & .Rows.Count).End(xlUp).Row
    For Each cell In .Range(.Cells(3, 2), .Cells(lLR, 2))
        Sheets("Template").Copy After:=Worksheets(Sheets.Count)
        Set sh = ActiveSheet
        On Error Resume Next
        sh.Name = cell.Value
        On Error GoTo 0
        sh.Range("C3") = cell.Offset(0, 1).Value
    Next 'cell
End With
End Sub

Regards, TMS

[mahen2]

TMS
" missing after Template. Apart from that working pefectly.

Thank you so much.

regards,
Mahen

[TMS]

Ah, sorry about that. It wasn't what I used to test

Glad you could sort it.

Regards, TMS



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[TMS]

Please mark your thread solved (as above). Thanks.