add worksheets to excel workbook using count defined name Solved/Closed

Question

Hello, 

I need to add worksheets based on a count which I defined as ADDTABS located in cell B4 on a worksheet called QRY-CMW.

The worksheets need to be added directly following the worksheet above.  

I also need the name of the workbook to be "TENANT #"

With the # being the ADDTABS number as the worksheets are added.


I've tried several options, but I am getting error messages.  

The first workbook has a 40 count in the ADDTABS cell.  I can't get past the error.  

:(

Thank you for your help.

TrowaD · Answer

Hi Raungst98,

This is what I get from your query:
You have a number in B4 on sheet QRY-CMW.
This number represents the amount of sheets you want to add after sheet QRY-CMW.

Example:
If the number 3 is in B4 the the first sheet will be created:
TABS: "QRY-CMW" - "TENANT 1"
Then the next:
TABS: "QRY-CMW" - "TENANT 2" - "TENANT 1"
And the next:
TABS: "QRY-CMW" - "TENANT 3" - "TENANT 2" - "TENANT 1"

This is done by the following code:
Sub RunMe()
Dim x, AddSheets As Integer

AddSheets = Sheets("QRY-CMW").Range("B4").Value

Do
    x = x + 1
    Worksheets.Add after:=Sheets("QRY-CMW")
    ActiveSheet.Name = "TENANT " & x
Loop Until x = AddSheets

End Sub 

If you rather have the following result:
TABS: "QRY-CMW" - "TENANT 1" - "TENANT 2" - "TENANT 3"
Then use this code:
Sub RunMe()
Dim x, AddSheets As Integer

AddSheets = Sheets("QRY-CMW").Range("B4").Value

Do
    x = x + 1
    Worksheets.Add after:=Sheets(Sheets.Count)
    ActiveSheet.Name = "TENANT " & x
Loop Until x = AddSheets

End Sub

Hopefully I understood you correctly and this helped you out.

Best regards,
Trowa

Add worksheets to excel workbook using count defined name

1 response

Subscribe To Our Newsletter!