Solved

Insert Worksheet Loop - Rename and unhide each sheet

Posted on 2011-03-19
10
350 Views
Last Modified: 2012-05-11
Hi there

I have a VBA macro that inserts X amount of a hidden worksheet into my workbook, however I can't seem to figure out how to unihide and rename these as they are being created by the loop. Can any VBA experts help me out please?
For Each c In Range("=Quotation!$A$6:$A$20")

    strName = "Item " & c.Value
    'Blank is the name of my template worksheet
    Sheets("Blank").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = strName
    ActiveSheet.Visible = True            

Next c

Open in new window

0
Comment
Question by:wellso
  • 6
  • 4
10 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
You are naming them in the loop ... do you want to later rename them?

Chris
0
 
LVL 2

Author Comment

by:wellso
Comment Utility
Yeah I'd like name them item + c.value as they are being created if possible
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
Item + c.value

Item is a string and you cannot algebraically add so can you explan what you want as a result as a text example?

Chris
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
BTW you aren't hiding them in yur loop due to .visible = true rather false or xlhidden

Chris
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
If I wasn't clear then you are creating a string as for example Assuming A6 contains myA6Text then

"Item myA6Text" and this is used to rename the newly inserted sheet.  The value updates for myA6Text with each time the loop proceeds.

A6 = myA6Text
First (Inserted) sheet becomes Item myA6Text by name
A6 = 22
First (Inserted) sheet becomes Item 22 by name

A7 = myA7Text
Second (Inserted) sheet becomes Item myA7Text by name

If the issue is with a rerun then either the sheets need deleting or you somehow need a note of the old name and then rename the sheet but first off to understand the problem.

Chris

Chris
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 2

Author Comment

by:wellso
Comment Utility
I meant to use & instead of +, sorry, too much JavaScrpt :)

SO if I have the range 1,2,3,4,5 my new sheets will be

Item 1
Item 2
Item 3
Item 4

I want to unhide the sheets as they are created, not hide them. The orignal 'Blank' statys hidden.
0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 500 total points
Comment Utility
Try for example

Chris
Dim sh As Worksheet
For Each c In Range("=Quotation!$A$6:$A$20")

    strname = "Item " & c.Value
    'Blank is the name of my template worksheet
    Sheets("Blank").Copy After:=Sheets(Sheets.Count)
    Set sh = Sheets(Sheets.Count)
    sh.Name = strname
    sh.Visible = True 'xlHidden

Next c

Open in new window

0
 
LVL 2

Author Comment

by:wellso
Comment Utility
Thanks bud thats pretty much it, but Item one seems to be a copy of a different sheet rather than''Blank'
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
Have you tried deleting all the item sheets because as a loop it is difficult to see how it could differ perhaps item 1 is left over from a different naming sequence?

Chris
0
 
LVL 2

Author Closing Comment

by:wellso
Comment Utility
Thankyou ever so much!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now