Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
A couple of months ago, someone on this forum helped me
put together this:Sub Add_Sheets()
Dim rCell As Range
Dim bTemplate As Workbook
Set bTemplate = Workbooks.Open("Mac
HD:Applications:Microsoft Office 2004:Templates:My
Templates:OneTitleBudgetTemplate.xlt", 0, True)
For Each rCell In
ThisWorkbook.Sheets("Sheet1").Range("A1:A33")
bTemplate.Sheets(1).Copy
After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name
= rCell.Value
Next rCell
bTemplate.Close False
End SubEssentially, it takes the data entered into a range of fields
on Sheet1, then opens up multiple worksheet tabs using a
template I created AND renames the tabs based on what's
been inputed on Sheet1. Sheet1 is pretty much a table of
contents.I'd like to add two more jobs to this script:
1) I'd like to take each tab name and automatically put it in
the body of the same worksheet as the tab (either in the
header or in field A1, etc.)
2) I'd like to auto link the table of contents (Sheet1) to each
corresponding tab (so when I click on the word 'Monkey' (in
Sheet1), it'll automatically open up to the 'Monkey' tab.Does this make sense? This is all happening in the same workbook, if that helps. I work in a non-tech industry where this sort of stuff is way beyond any of us. LOL. So any help
will be much appreciated!
Untested:
Sub Add_Sheets()
Dim rCell As Range
Dim bTemplate As Workbook
Set bTemplate = Workbooks.Open("Mac HD:Applications:Microsoft Office 2004:Templates:My Templates:OneTitleBudgetTemplate.xlt ", 0, True)
For Each rCell In ThisWorkbook.Sheets("Sheet1").Range("A1:A33")
bTemplate.Sheets(1).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
With ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
.Name = rCell.Value
.Cells(1) = rCell.Value
End With
Hyperlinks.Add rCell, "", rCell.Value & "!A1"
Next rCell
bTemplate.Close False
End Sub
0 
Hmm...it seems to get stuck on this line:
.Cells(0) = rCell.Value
Am I suppose to enter in something where the zero is
located?
0
Almost!! Now it highlights this line:
Hyperlinks.Add rCell, "", rCell.Value & "!A1"
And says "Object Required". Am I suppose to put something
in between the two quotation marks?Thanks so much for all your help!!
0
I Don't know what the problem is. The line works on my machine. It could be a difference between Office versions, or it could be a difference between Windows and Macs.
Either way, I don't have anything useful to add, other than it seems to be complaining about the rCell, but why it does when the next line says Next rCell, I don't know.
0  |
 .VBS Script To View IP Ad...
|
Exporting Clarion files f...
|
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
