PDA

View Full Version : Solved: Alter Code from a KB Entry: Table of Contents



Anne Troy
01-28-2006, 01:31 PM
Hi, guys!

I want to use Zack's code here:
http://vbaexpress.com/kb/getarticle.php?kb_id=120

I'd like to incorporate this code into the beginning of it:
http://vbaexpress.com/kb/getarticle.php?kb_id=72

But I want it to ignore the worksheets called:
"Dashboard"
"TOC"
"IngredientsCostSheet"

I think it already ignores the TOC worksheet.
I'd like it to leave these 3 sheets as the first 3 worksheets in the workbook.
_________________________________

Secondly, I want the following code to insert the new sheet at the end of all sheets:
Sheets("RenameAsProductName").Visible = True
Sheets("RenameAsProductName").Select
Sheets("RenameAsProductName").Copy after:=Sheets(2)
Sheets("RenameAsProductName").Select
Sheets("RenameAsProductName").Visible = False

and can't figure out how to change that 3rd line to be after "last" or something.

Thanks in advance!!

XLGibbs
01-28-2006, 01:51 PM
Piece of cake DB...Give me 10 minutes..max :)

Is there going to be a fixed amount of sheets or an ever growing amount of sheets with respect to part 2.

Anne Troy
01-28-2006, 01:52 PM
Part 2: ever-growing

Thanks, dude!

mdmackillop
01-28-2006, 02:03 PM
Hi DB
I see Gibbs is on the job, but FYI, after last is done as after:=sheets(sheets.count)

Anne Troy
01-28-2006, 02:07 PM
Damn. You guys are so great. :)
So, how could I throw up a dialog to rename that sheet I just added?
Here's the code I have now:
Sub MakeNewProduct()
Sheets("RenameAsProductName").Visible = True
Sheets("RenameAsProductName").Select
Sheets("RenameAsProductName").Copy after:=Sheets(Sheets.Count)
Sheets("RenameAsProductName").Visible = False
End Sub

XLGibbs
01-28-2006, 02:38 PM
As already shown above...copy to last position


Sub CopytoLast()

With ActiveWorkbook
Sheets("IngredientCostSheet").Copy After:=Sheets(.Sheets.Count)
End With

End Sub


If you have all the sheets to skip at the beginning (sheets 1 to 3 left to right) you can just change the
FirstWSToSort = 2
to
FirstWSToSort = 4

Else a slight modification will skip over the named sheets..


For M = FirstWSToSort To LastWSToSort
For n = M To LastWSToSort
If UCase(Worksheets(n).Name) <> "DASHBOARD" And _
UCase(Worksheets(n).Name) <> "TOC" And UCase(Worksheets(n).Name) <> "INGREDIENTCOSTSHEET" Then

If SortDescending = True Then
If UCase(Worksheets(n).Name) > UCase(Worksheets(M).Name) Then
Worksheets(n).Move before:=Worksheets(M)
End If
Else
If UCase(Worksheets(n).Name) < UCase(Worksheets(M).Name) Then
Worksheets(n).Move before:=Worksheets(M)
End If
End If

Else: Worksheets(n).Move before:=Worksheets(M)

End If
Next n
Next M



You can call the CreateTOC sub from the end of this sub...

By

Call CreateTOC

Sample attached.

XLGibbs
01-28-2006, 02:44 PM
What kind go dialog? Btw, you don't need to make the sheet visible, select it, then copy it. The line of code I provided will copy it regardless...

Do you want an input box?

Sub CopytoLast()
With ActiveWorkbook
Dim shName As String
shName = InputBox("Enter a name for the new sheet", "New Sheet Name?", "IngredientCostSheet")

Sheets("IngredientCostSheet").Copy After:=Sheets(.Sheets.Count)
If Not ActiveSheet.Name = shName Then
ActiveSheet.Name = shName
Else
ActiveSheet.Name = shName & "(copy)"

End With

End Sub

Ooops...I used a different sheet name in my examples, I am sure you picked up on that :thumb:mkay

Anne Troy
01-28-2006, 04:29 PM
Gibbs: Thanks sooo much! I was creating a free download for my website. I found out that I missed a huge piece of it, and will have to revisit it. I'm sure your code will be great and I'll come back to it in a few days. Thanks again!!

Anne Troy
01-28-2006, 04:30 PM
You, too, MD!!