Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating worksheets from another worksheet
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 ------------------- -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating worksheets from another worksheet
Snoopy369 wrote:
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating worksheets from another worksheet
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 -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating worksheets from another worksheet
Snoopy369 wrote:
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating worksheets from another worksheet
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 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 --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating worksheets from another worksheet
Snoopy369 wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating 2 new Worksheets | Excel Discussion (Misc queries) | |||
Creating new worksheets | Excel Worksheet Functions | |||
creating sub worksheets | Excel Worksheet Functions | |||
Creating worksheets within a worksheet | Excel Discussion (Misc queries) | |||
Creating/Naming New Worksheets Based on Select Cells in Master Worksheet | Excel Worksheet Functions |