• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 372
  • Last Modified:

Insert Worksheet Loop - Rename and unhide each sheet

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
wellso
Asked:
wellso
  • 6
  • 4
1 Solution
 
Chris BottomleyCommented:
You are naming them in the loop ... do you want to later rename them?

Chris
0
 
wellsoAuthor Commented:
Yeah I'd like name them item + c.value as they are being created if possible
0
 
Chris BottomleyCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Chris
0
 
Chris BottomleyCommented:
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
 
wellsoAuthor Commented:
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
 
Chris BottomleyCommented:
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
 
wellsoAuthor Commented:
Thanks bud thats pretty much it, but Item one seems to be a copy of a different sheet rather than''Blank'
0
 
Chris BottomleyCommented:
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
 
wellsoAuthor Commented:
Thankyou ever so much!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now