Im looking for Excel VBA code to automatically duplicate Tab 1 in workbook when a table in Tab 2 is populated with a name

Question

VBA code needed:

I have 2 tabs in a workbook. Tab 1 (DIST "A") is a formatted spreadsheet & Tab 2 (RFP) has a table with a column called "Distributor". When I enter names under the Distributor list, could be 1 or 30 differnet names, I want VBA to automatically duplicate Tab 1, rename the duplicated sheet to whatever name I entered, and send it to end of workbook.

Here's my current code, its duplicating the ACTIVE SHEET, Tab 2 and not Tab 1 (what I need)

Sub Copyrenameworksheet()

Dim ws As Worksheet
Set wh = Worksheets(ActiveSheet.Name)
ActiveSheet.Copy After:=Worksheets(Sheets.Count)
If wh.Range("A1").Value <> "" Then
ActiveSheet.Name = wh.Range("A1").Value
End If
wh.Activate
End Sub

First step: add Option Explicit to the top of the module. You declare ws and then Set wh. — BigBen
– BigBen, Commented Feb 4, 2020 at 17:50
Thank you, im new to VBA so I still don't understand how to implement this. — Gunit
– Gunit, Commented Feb 4, 2020 at 18:13
@Gunit Keep reading. There is quite a bit of documentation around VBA and implementation, e.g., where to put a change event. — Cyril
– Cyril, Commented Feb 4, 2020 at 18:32

Shelty · Accepted Answer · 2020-02-04 18:49:37Z

Apart from very the nice documentation provided by BigBen, you may look at Worksheet Change Event tutorial.

It should be a bit easier to understand with this step-by-step introduction.

Basically what should be done could be the following:

Using 'Intersection' method from the code in the tutorial/documentation check whether your list has been changed.
Check whether the added worksheet already exists [optional]
Duplicate the worksheet and set its name to Target.Value

Put the following code into sheet object for 'RFP' worksheet (see the tutorial I linked). Substitute "A1:A3" range with the range of your distributor list. When you enter new distributor, a message box with the name of the distributor should pop up.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:A3")) Is Nothing Then
        Application.EnableEvents = False


            MsgBox (Target.Value)


        Application.EnableEvents = True
    End If
End Sub

Now write code that duplicates the worksheet and changes its name to Target.Value and basically all you need to do is to put it instead of MsgBox.

Collectives™ on Stack Overflow

Im looking for Excel VBA code to automatically duplicate Tab 1 in workbook when a table in Tab 2 is populated with a name

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related