• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 386
  • 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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