How to automatically create new worksheet based on selecting data through Data Validation drop down

[Sarrah91]

I have budgets of several departments and I need to send them details.

I have a master file with data validation, now I want to create multiple worksheets when I change or toggle with the data validation input. Is this possible?

I am currently doing it through the conventional way and it's tedious, taking the data to a separate worksheet, breaking the links saving to a separate workbook and then sending it over to the department.

For instance, from the screenshot below, let's say when I select 'A2' from the data validation list, I want a new sheet to be created with the name of 'A2'.

 

[mumps]

Do you want the new sheet to be blank? It is hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Sarrah91]

Do you want the new sheet to be blank? It is hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

I want the new sheet to contain data taken from D2 to F7.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Select a value in B1.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "B1" Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Range("D2:F7").Copy
    Sheets.Add after:=Sheets(Sheets.Count)
    ActiveSheet.Name = Target.Value
    Sheets(Target.Value).Range("A1").PasteSpecial
    With Application
        .CutCopyMode = False
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

 

[Sarrah91]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Select a value in B1.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "B1" Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Range("D2:F7").Copy
    Sheets.Add after:=Sheets(Sheets.Count)
    ActiveSheet.Name = Target.Value
    Sheets(Target.Value).Range("A1").PasteSpecial
    With Application
        .CutCopyMode = False
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

This is pure code. IT WORKED. Thank you sooo much. May you achieve all the success in the world. Only one thing I also have a bar graph which isn't being copied, but I think I will add it manually. I also want to learn coding, how to start?

 

[Sarrah91]

This is pure code. IT WORKED. Thank you sooo much. May you achieve all the success in the world. Only one thing I also have a bar graph which isn't being copied, but I think I will add it manually. I also want to learn coding, how to start?

*gold

 

[mumps]

You are very welcome.
I found that the best way to learn VBA on your own is to use on-line tutorials, searching the web for specific areas of interest and following forums such as this one. The volunteers on sites such as this one are invaluable at providing help. I would suggest you keep a file of codes that you find useful and in this way you can build up a library that you can refer to. A lot is also trail and error. The following links may be of help:

VBA for smarties

www.snb-vba.eu

Excel VBA Dictionary - A Complete Guide - Excel Macro Mastery

This post contains everything you need to know about using the VBA Dictionary. There are tons of code examples including a real world application.

excelmacromastery.com

Excel VBA Array - The Complete Guide - Excel Macro Mastery

This post provides everything you need to know about the Excel VBA Array. Includes a quickVBA Array reference guide and 50+ VBA Array examples.

excelmacromastery.com

New to Excel Programming and looking for learning materials

Hello Please I am new to excel programming and would like to get started. Can anyone help me with basic study materials for a beginner. Mod Edit (10 August 2018): PLEASE NOTE:

www.excelforum.com

The more you practise, the better your get at it. Good luck with it!!!!