fill a particular cell on a series of worksheets from a lookup tab

In the first worksheet of my workbook I have a list of part numbers listed in 
a column, say approximately 20 part numbers.  I would like to have one 
worksheet for each part number, with cell A1 containing the part number for 
that worksheet. 

So if my list was:

pn1
pn37
pn469
pn2034

then cell A1 of the first worksheet in the series would ="pn1",  cell a1 in 
the second worksheet of the series would ="pn37"

Is there an easy way to populate these worksheets from the list?

Thanks,

Rob Samples
0
br549 (4)
1/6/2005 4:01:02 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
412 Views

Similar Articles

[PageSpeed] 37

You could use a little macro:

Option Explicit
Sub testme()
    Dim testWks As Worksheet
    Dim MstrWks As Worksheet
    Dim myRng As Range
    Dim myCell As Range
    
    Set MstrWks = Worksheets("sheet1")
    With MstrWks
        Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
        For Each myCell In myRng.Cells
            Set testWks = Nothing
            On Error Resume Next
            Set testWks = Worksheets(myCell.Value)
            On Error GoTo 0
            
            If testWks Is Nothing Then
                'add it
                With Worksheets
                    Set testWks = .Add(after:=.Item(.Count))
                    On Error Resume Next
                    testWks.Name = myCell.Value
                    If Err.Number <> 0 Then
                        MsgBox "Illegal name: " & myCell.Value & vbLf & _
                               "Please correct worksheet named: " _
                               & testWks.Name
                        Err.Clear
                    End If
                    On Error GoTo 0
                    testWks.Range("a1").Value = myCell.Value
                End With
            End If
        Next myCell
    End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


br549 wrote:
> 
> In the first worksheet of my workbook I have a list of part numbers listed in
> a column, say approximately 20 part numbers.  I would like to have one
> worksheet for each part number, with cell A1 containing the part number for
> that worksheet.
> 
> So if my list was:
> 
> pn1
> pn37
> pn469
> pn2034
> 
> then cell A1 of the first worksheet in the series would ="pn1",  cell a1 in
> the second worksheet of the series would ="pn37"
> 
> Is there an easy way to populate these worksheets from the list?
> 
> Thanks,
> 
> Rob Samples

-- 

Dave Peterson
0
ec357201 (5290)
1/7/2005 12:42:54 AM
Reply:

Similar Artilces:

Adding a formula to the same cell (H5) on every tab
I have an inventory spreadsheet with 125 tabs. The tabs are numbered 1 through 125. The are identical except for the data below the column headings. If I wanted to put a formula in H5 on every tab, can it be done other than manually opening every tab and typing it? One additional question: If I add a Summary Tab, how could I show the value of a specific cell on each tab without manually entering it? I show the formula I'm using bring B3 to the summary for every tab: A B 1 Unit Value 2 1 ='1'!B3 3 2 ='2'!B3 4 3 ='3'!B3 5 4 ='4'!B3 6 5 ='5'!B3 7...

display cell content based on date criteria
My worksheet contains several columns of data, first column being DATE. each day new information is filled in column B to K, but in Column A, all the dates are already filled up, say 1 Jan 2009 to 31 Dec 2009. I want that a date should not be visible before that date actually arrives, i.e. on 5th jan, I should see on first 5 rows (jan 1 to jan5) On 30th Dec, I should see all rows from Jan 1 to Dec 30, but not Dec 31. Is is possible ? In cell A2 enter the first date...Enter the below formula in A3 and copy down as required.. =IF(A2<>"",IF(A2+1<=TODAY(),A2...

Lookup Wizard changing Data Type
I am trying to make Lookups from Table Field to Table Field and generally it seems OK. However the LookupWizard is changing the Data Type from Text to Number (I guess it's looking at the ID?) Data picked from Combo Box 'looks' OK in Table view A Query view is asking for a number but displays the Text field value when a valid ID number is added. Viewing Results in FrontPage Database Wizard, and using hand coded .asp querie, it's showing the ID field value. I can see why... Any ideas? Merci. Yes, the wizard isn't the problem it's the use of Lookup Fields in ...

Investmentmap.com : Learning Series :Richard Donchian
A must Read for the Successful Trader Richard Donchian - A Trading Legend's Strategy Unveiled... Richard Donchian was born in Hartford, Connecticut in September 1905 ( over 100 years ago) and although the vast majority of traders have never heard of him yet, he is one of the most influential traders of all time and the father of technical trend following. Many modern trend following systems, such as the Turtle Trading system, are based on his work and legendary trader Richard Dennis was a huge fan and Ed Seykota used him as an inspiration. Richard Donchian didn't begin trading his ...

open new tab on the same window
I know that I have asked this before but it had been a while. I close some of the unwanted toolbar, I've noticed that I can't open a new tab on the same window when I click on my mouse on the right side. All I see is open to new window but no new tab. I have Window Vista with IE8. -- Lauria ...

Help; how do i change the waiting circle (in IE tab) to anything e
I am making a website and i have a lot of wait time for my server-side pages to do their work. The waiting circle icon gives me a headache so I would like to change that to something else please. I remember IE6 had a great Microsoft Flag logo that would wave while a page would load. Could I get IE8 to use this logo? Sorry Trozza, you can't. "Trozza Mited" <Trozza Mited@discussions.microsoft.com> wrote in message news:9C401644-2391-4317-92BD-D7F3DFD2B06C@microsoft.com... > I am making a website and i have a lot of wait time for my server-side > p...

Coping part of a cell content into a seperate cell
Hi I have two cells, one containing first and middle name and another one with surname. I want to combine the first name and surname into a separate cell, can you advise how I can just copy the first name and miss out the middle name please?? Thanks Caz Hi, I assume that the midle name is separated by a space from the first name and is in column A and the last name in column B =TRIM(LEFT(A2,FIND(" ",a2)-1))&" "&B2 "Caz H" wrote: > Hi > I have two cells, one containing first and middle name and another one with &g...

Concatenate then Fill Down
Hi I have the VBA CODE of 24 lines Range("A2").Formula = Range("AA2") & " / " & Range("AG2") & " / " & Range ("AI2") Range("B2").Formula = Range("AA2") & " / " & Range("AG2") & " / " & Range ("AI2") Range("C2").Formula = Range("AA2") & " / " & Range("AG2") & " / " & Range ("AI2") ,,,,,, Range("V2").Formula = Range("AA2"...

Macro Relative Cells
Hi Guys. Ok i'll try and explain this as simply as i can. I want to create an expenses sheet which allocates the net amount to individual cells depending on which cell is highlighted, by clicking a button. So in cell B10 i put my gross amount = 100.00 C10 works out the VAT for me = 14.89 Now the user highlights the cell (in different columns) under the appropriate heading eg. 'Petrol' and clicks the button at the top of the sheet which automatically puts 85.11 in the highlighted cell. Sounds simple. I have created the button and i have created the Macro: A...

Copying worksheets with hyperlinks to named cells
I have a spreadsheet that I am working to collect data for several different products. I developed a skeleton worksheet that will be copied within the same workbook and re-named with each individual product. In the skeleton sheet, I have hyperlinks near the front of the sheet that reference to areas later in the sheet (via named cells). I copied this skeleton sheet and gave the copy another name ('product 1' for example). When I click the hyperlinks in the 'product 1' worksheet, it links back to the 'skeleton' worksheet. Is there an easy way to have the lin...

View Old Worksheets
We have a customer that would like to look at an old worksheet in HQ. But the worksheet status viewer only shows the last 140 items. is there anyway to access historical worksheets? Version is 1.3. Thanks and regards, Matt huh ??? how does it possible? only 140 items??? r u viewing it thru ??? View-->worksheet Status if yes...check with the flags maybe you only have selected blue flag (Approved for processing) ; most of the old worksheet are in Green (Completed successfully) or Red (with Warnings) or others.. but if you chose to view it thru Admin-->Worksheets and it shows only...

Disappearing scales on bar charts with multiple series
I am trying to create a bar chart with multiple series in Excel 2003. I have x and y data, but the x-axis has to be the same for all series or it takes the x-values of the last series created. My problem is not the scale. It is getting two series to appear independent of each other. Using bar charts forces a comparison of the two series, but I simply need to plot data for each series. Should I be using a different chart type? If so, what is it? -- Thanks, Patrick Hi, If you have true x and y data maybe you should be plotting an x and y chart. You can format the series so only mar...

how to share a row throughout the worksheet?
What do yo mean? "asd" wrote: > ...

Hidden Worksheets at Start-up of File Q
All I have the following code which fires on opening of my workbook, problem is I am getting an 'object defined' error. I think its because I have hidden worksheets within the workbook, well at least it worked when I had none of the sheets hidden. How can I work-around this leaving the worksheets hidden? Thanks Private Sub Workbook_Open() Application.ScreenUpdating = False Dim sh As Worksheet Sheets("Sales Mix").Select Cells.Select Selection.ClearContents Range("A1").Select Selection.QueryTable.Refresh BackgroundQuery:=False Range("...

transferring data/text to another worksheet
I have two columns/rows with the following data on Sheet1: Column: A B Row: 1 178 buy 2 buy 3 185 buy 4 140 sell 5 sell 6 sell 7 130 buy I want to tranfer to sheet 2, all the cells in column A that have number value and if column A has a number, then transfer the text i column B. This is what I want sheet 2 to look like: ...

Cell Formatting #5
Is there a way to format a cell so that it will automatically round either up or down? I am currently using Excell 2002. Thanks format "number" seems to work just fine Moshe Paul, Is this what you need? This will not limit you in just rounding decimal places. Format>>Cell>>Number>>Custom To make 1,234,567.00 as 1,125 type #,##0, as 1,1234.57 type #,##0.00, You can try other formatting just try it. Please note that this formatting will only change the way you see the number is displayed on the screen but does not change the actual value. Hope this...

Basic--How do I make dates look a certain way in a cell?
Here's what I'd like to do: I'd like to type 41507 in a cell, and have it display as "4/15/07" or "04/17/07". Instead, it pops up some crazy date like 7/31/85. I've figured out why this is (the number indicates which day since January 1, 1900, and inserts that date). But I can't figure out if it is possible to do what I want. Is there a technique for typing in a date without slash marks or periods, and have these appear in the cell automatically? Thanks for any help you can give. ddc This will *look* like the date you want to display, but it ce...

Copying Merged Excel cells to Word.
This was posted to excel.programming but no response received :-( Hopefully some kind person can help.... I have an excel worksheet that contains some merged cells which I need to copy to Word I open the word doc with "Set Wordapp = CreateObject("Word.Application")", copy the required area of the worksheet using "selection.paste" and format the word page. This is OK except for any merged cells which copy across as indivual cells in the resultant table in Wword. How can I overcome this problem as I need to keep the same cell format in the Word table?? I hav...

Lookup equals null
Hi I need help with a simple bit of Jscript. I need to hide a tab when a person opens the service actvity form and the 'Service' lookup does not contain data. I used the Onload event and the following bit of script: { if (crmForm.all.serviceid.DataValue[0].name == null) crmForm.all.tab1Tab.style.display = 'none'; } crmForm.all.serviceid.FireOnChange(); This does not seem to work properly. I think there is a problem with the ==null part of the script. The OnChange event works fine and is: if (crmForm.all.serviceid.DataValue[0].name== 'Support') crmForm.all.tab1Ta...

tab in access
-- I added a tab control to a form what do i do so it should be from right to left? Gal On Sun, 21 Mar 2010 02:57:01 -0700, Gal <Gal@discussions.microsoft.com> wrote: That is currently not supported. Why do you need it? -Tom. Microsoft Access MVP > >-- I added a tab control to a form what do i do so it should be from right >to left? >Gal I am programing for a company in Israel and they write in hebrew plz help me if u can -- Gal "Gal" wrote: > > -- I added a tab control to a form what do i do so it should be from right ...

Opening tab-delimited files in Excel... how to stop the autoformat of dates?
Hello, I'm doing bioinformatics work in which I often download tab-delimited files describing genomes and open them in Excel. Unfortunately, Excel decides that genes with names like APR1 and SEP7 are actually dates, and reformats them as such. This is a huge headache, as I often don't notice these problems until much later. Is there anyway to completely disable this date autoformatting? I've searched high and low and have yet to find a solution. Thanks! P.S. I'm using Excel 2002 -- salamander ------------------------------------------------------------------------ sa...

How to see calculation and heading in same cell.
I would like the cell to perform a calculation and then display the answer as will as a heading. In other words the answer and the heading will appear in the same cell. Perhaps you mean something like ="Heading Name: "&A1+B1 ******************* ~Anne Troy www.OfficeArticles.com "Jeracho" <Jeracho@discussions.microsoft.com> wrote in message news:F34BACB9-E2DA-448A-924D-46A475F98F91@microsoft.com... > I would like the cell to perform a calculation and then display the answer as > will as a heading. In other words the answer and the heading will appear in...

Batch delete hidden worksheets
I have over 50 hidden worksheets (in 1 workbook) that I want to delete. Is there a shortcut to either delete all hidden sheets or at leas unhide them in a batch? I don't have VBA skills. Thanks for any help -- Message posted from http://www.ExcelForum.com something like this Sub deletehiddenwbs() Application.DisplayAlerts = False For Each ws In Worksheets If ws.Visible <> True Then ws.Delete Next Application.DisplayAlerts = True End Sub Don Guillett SalesAid Software donaldb@281.com "escribe >" <<escribe.zvatc@excelforum-nospam.com> wrote in message news:...

Help me! There is problem with cells view...
Hello! By default when I write some text in cell and the text is longer then cell's width, I can see his contents in next cells. But in one excel worksheet when I sellect all cells in worksheet and then draging them it can no longer seen all contents in one cell. In entire worksheet the cells have wrap text turned off, there are no merged cells. In another worksheet with the same cell properties, the text overflow to the next right cells of course the cells behind the edit one are empty. I don't know what is going on. And I don't know how this was happened. It is very import...

Money 2005
Hi, I am having a problem whereby, whenever I goto the Budget tab, Money freezes/hangs and takes up 100% of one of my CPU's. I have been able to get it over the a basic budget, by removing all my accounts one at a time from tools -> settings -> budget settings -> budget accounts. then changing to basic. However if I have multiple accounts it freezes whenver I try to do anything with the budget. Many thanks Altonius "Altonius" <Altonius@discussions.microsoft.com> wrote in message news:B4B8AF1A-4B19-4F25-BE2E-2DA0951B5235@microsoft.com... > I am having ...