Creating worksheets from another worksheet

[snoopy369]

I'm trying to add functionality to my spreadsheet that takes a lis
present on the main worksheet page, of undeterminate length (but no
infinite -- maybe 10 or 15 at most), and, if the worksheets do no
already exist, creates an individual worksheet for each item on th
list.

The list consists of different names, with a heading, like so:
Vendor:
Tribune
Times
Sentinel
Tribune
Sentinel
Post
Times
Tribune

etc., and the names may vary.

I wrote some code to try and do this, and have gotten as far a
creating new sheets in the correct number (thus my loop is correct, an
I'd assume my identifying cells is correct) but it does not correctl
rename the sheets (and thus creates too many sheets). I only want on
sheet per name (even if the name repeats, as it will likely). So, if
have 7 names (after "Vendor:") but only 4 unique names (3 repeats) i
creates seven new worksheets named "Sheet1" to "Sheet7". Not sure wh
the name isn't working ...

here's my code so far:
(Important part first, then the entire thing)

Code
-------------------

Do Until ((Left(wksData.Cells(intRow, VendorCol), 3) = "End") Or (intRow > 30))
If (Left(wksData.Cells(intRow, VendorCol), 4) = "Vend") Then
Set wks = Worksheets(wksData.Cells(intRow, VendorNameCol).Value)
If Err > 0 Or wks Is Nothing Then
Err.Clear
Worksheets.Add after:=Worksheets(Worksheets.Count)
Range("A1") = wksData.Cells(intRow, VendorNameCol).Value
ActiveSheet.Name = wksData.Cells(intRow, VendorNameCol)
MsgBox "I created a sheet"
End If
End If
intRow = intRow + 1
Loop

-------------------


Entire Thing:


Code
-------------------

Sub WorksheetCreating()
Dim wks As Worksheet, wksData As Worksheet
Dim intRow As Integer, intRowL As Integer
Dim strSheet As String
Application.ScreenUpdating = False
Worksheets("NewspaperLog").Activate
Set wksData = ActiveSheet
Dim VendorCol As Integer, VendorNameCol As Integer
VenderNameCol = 16 'this is the column that the actual name is in
VendorCol = 15 ' this is the column of "Vendor", with the word "Vendor" in every cell in 15 that has a vendor name in 16
intRow = 3
ActiveSheet.Name = "NewspaperLog"
On Error Resume Next
MsgBox "We got here ok 0"
Do Until ((Left(wksData.Cells(intRow, VendorCol), 3) = "End") Or (intRow > 30))
If (Left(wksData.Cells(intRow, VendorCol), 4) = "Vend") Then
Set wks = Worksheets(wksData.Cells(intRow, VendorNameCol).Value)
If Err > 0 Or wks Is Nothing Then
Err.Clear
Worksheets.Add after:=Worksheets(Worksheets.Count)
Range("A1") = wksData.Cells(intRow, VendorNameCol).Value
ActiveSheet.Name = wksData.Cells(intRow, VendorNameCol)
MsgBox "I created a sheet"
End If
End If
intRow = intRow + 1
Loop

MsgBox "We Got here ok 1"
On Error GoTo 0
Worksheets(2).Select
intRow = 3
' don't forget to put the data copying stuff here
MsgBox "We Got Here ok 2"
Application.ScreenUpdating = True
End Sub

 

[Bill Manville]

Not sure why the name isn't working ...

Nor am I, but I would try putting Err.Clear before the Set wks = ...
line; you may be picking up a different error than the one you were
looking for.


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

 

[snoopy369]

I'm pretty confident it's trapping the right error, at least in that i
calls the loop the correct number of times (well, given that the name
aren't being changed ...)

I also noticed that when I tried to MsgBox the value from the cell I'
naming it based on, it refuses to. Flat out, it just ignores my msgbo
line entirely. ie:

MsgBox wksData.Cells(intRow, VendorNameCol)

or same .value or same .Text

none of these work at all, and if I do
MsgBox "I created a sheet for " & wksData.Cells(intRow,VendorNameCol
(and .value, and .Text)
it still refuses to write anything in a msgbox (even the string at th
beginning) ...

Thanks!
-Jo

 

[Bill Manville]

Flat out, it just ignores my msgbox
line entirely. ie:

MsgBox wksData.Cells(intRow, VendorNameCol)

Which shows that the line is encountering an error which is not being
reported because of On Error Resume Next.
It really is not good practice to leave On Error Resume Next active
over many statements - for the excellent reason that it obscures what
is going on.

It is also good practice to have Option Explicit at the top of your
module. If you had, you would have had a compilation error saying that
VenderNameCol is undefined.

Dim VendorCol As Integer, VendorNameCol As Integer
VenderNameCol = 16 'this is the column that the actual name is in

Note the typo in the second line.
This means that VendorNameCol has the default value of 0, not 16.

So every reference to wksData.Cells(intRow, VendorNameCol) generates an
error (because there is no column 0) and you happily ignore all the
errors.

Had you tried stepping through the code using the debugger you would
have been able to see that VendorNameCol was 0, and that could have led
you to the solution.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

 

[snoopy369]

Dim VendorCol As Integer, VendorNameCol As Integer

Note the typo in the second line.
This means that VendorNameCol has the default value of 0, not 16

Doh! Good point. Last time I borrow a bit of code without knowing
what each line precisely does ...

(well, probably not. But it's a good learning experience!)

Thanks!!! Will test it out, should work with that correction, since
that makes a LOT of sense (the 0 column is probably blank ... and the
VendorCol was correct and thus doing the right number

-Joe

 

[Bill Manville]

the 0 column is probably blank

There is no 0 column
Each reference to it will have generated an error
On Error Resume Next means you didn't see the errors.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup