Trying to create consecutive tabs from multiple macro assigned buttons

[d_max_c]

Hi,

In Excel 2010 I need to create two macros within a template that will each operate from a seperate button to create a single new numbered tab with each button press. The workbook template, to begin with, consists of a single tab named 'Master' and two seperate buttons called 'New Pre-Sale Load Tab' and 'New Post-Sale Load Tab'.

The first press of the 'New Pre-Sale Load Tab' button will create a new tab named 'Pre-Sale Load 1', the second press creating a new tab named 'Pre-Sale Load 2', the third press 'Pre-Sale Load 3', and so on up to a minimum of 12 additional tabs.

The first press of the 'New Post-Sale Load Tab' button will create a new tab named 'New Post-Sale Load 1', the second press creating a new tab named 'New Post-Sale Load 2', the third press 'Pre-Sale Load 3', and so on up to a minimum of 12 additional tabs.

The Pre-Sale Load tabs will always be created before the Post-Sale Load tabs.

I am fine with creating macro assigned buttons, I just can't find assistance for the above scenario. Can someone please help?

Thanks,
Dave

 

[bertie]

Hi and welcome to the forum.

Why now create both PRE and POST worksheets at the same time. This will eliminate the need for two command buttons.

You will need somewhere to store the count of existing worksheets.In the test code I have use Sheet1!B1.

[COLOR=darkblue]Sub[/COLOR] InsertNewSaleSheets()
   [COLOR=darkblue]Dim[/COLOR] salesCounter [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]


   [COLOR=green]'get count of exisiting worksheets[/COLOR]
    salesCounter = Sheets("Sheet1").Range("B1").Value + 1


   [COLOR=green]'add new Pre Sales Sheet[/COLOR]
   Worksheets.Add After:=Worksheets(Worksheets.Count)
   ActiveSheet.Name = "Pre-Sale Load " & salesCounter


   [COLOR=green]'add new Post Sales Sheet[/COLOR]
   Worksheets.Add After:=Worksheets(Worksheets.Count)
   ActiveSheet.Name = "Post-Sale Load " & salesCounter
   
   [COLOR=green]'update worksheet counter on worksheet[/COLOR]
   [COLOR=darkblue]With[/COLOR] Sheets("Sheet1")
      .Range("B1").Value = salesCounter
      .Select  [COLOR=green]'naviagte back to sheet1[/COLOR]
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]