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 - Last answered 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

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!