Report

Add worksheets to excel workbook using count defined name [Solved]

Ask a question raungst98 1Posts Sunday April 24, 2016Registration date April 24, 2016 Last seen - Latest answer on Apr 27, 2016 01:57AM
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.
See more 
Helpful
+0
moins plus
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
raungst98- Apr 27, 2016 01:57AM
Thank you! This works very well. I really appreciate the assist!

R
Reply
Add comment

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.

Not a member yet?

sign-up, it takes less than a minute and it's free!