VBA help

floydster123

New Member
Joined
Mar 21, 2013
Messages
2
Hi,

I have a sheet called template which contains a drop down list of clients(B3), the information in the sheet changes when a different client is selected. I need to create a copy of the template sheet for each of the clients in the list and I'd also like it to change the name of the sheet to the client selected.

I've been scouring the message board but I haven't found anything.

Please could somebody help.

Thanks
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Hi and welcome to the board.
There are a number of ways to achieve this and the best will depend on how you're doing a few things.
1) What do you mean by 'drop down list'? Are you using data validation in B3? Or is it a combobox perhaps? (If it's anything like a combobox then which toolbar did it come from?)
2) If whatever is in B3 has a source range tied to it, then what's that range?
3) Do you want the new sheet(s) added (for example) one at a time when a value in the drop down is selected? Or do you want them all added at once?
4) What version of excel are you using?

I believe this (and likely more) info is needed in order to know what direction to take to get you where you want to be.
 
Upvote 0
Hi and welcome to the board.
There are a number of ways to achieve this and the best will depend on how you're doing a few things.
1) What do you mean by 'drop down list'? Are you using data validation in B3? Or is it a combobox perhaps? (If it's anything like a combobox then which toolbar did it come from?)
2) If whatever is in B3 has a source range tied to it, then what's that range?
3) Do you want the new sheet(s) added (for example) one at a time when a value in the drop down is selected? Or do you want them all added at once?
4) What version of excel are you using?

I believe this (and likely more) info is needed in order to know what direction to take to get you where you want to be.

Hi, sorry for the lack of info.

1) Data validation in B3.
2) The source range is from another sheet called 'CLIENT LIST'!$A$5:$A$74.
3) That would be cool if it could be created everytime I select a value in the dropdown.
4) 2010.

Thank you

 
Upvote 0
OK, this was written in excel 03, but I can't imagine why it shouldn't work with 010.
Right click the sheet tab and choose View Code.
Paste this code into the (white) code pane that opens up.
Press Alt+Q to close the vb editor. (assuming that still works the same in 010...)
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) <> "B3" Then Exit Sub
If Len(Target.Value) = 0 Then Exit Sub

'''/// Search for a sheet already using the selected name.
For Each Sht In Worksheets
  If Sht.Name = Target.Value Then
    Rply = MsgBox("There is already a sheet named " & Target.Value & "." & vbNewLine & _
           "We can't have sheets with duplicate names so no new sheet will be created." & vbNewLine & vbNewLine & _
           "Would you like to select sheet " & Target.Value & "? (Click Yes)" & vbNewLine & _
           "If you would rather stay on sheet " & ActiveSheet.Name & " then click No.", vbYesNo, "Sheet Naming Error...")
    If Rply = vbYes Then Sheets(Target.Value).Select: Exit Sub
    If Rply = vbNo Then Exit Sub
  End If
Next Sht

'''/// No sheets were found with the selected name so a new sheet gets created & named.
Sheets.Add
ActiveSheet.Name = Target.Value

End Sub

Hope it helps. (especially with the version differences... let us know if it doesn't.)
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top