Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Copying list item (a bit less vague)
Ok, I have my first sheet named 'List' with a list of names on in in cell c3 downwards. The workbook also contains several other sheets after the first one with a template on each and an empty cell ('C3') on each. How can I copy a value from the top of my list on my sheet named 'list' onto each sheet which follows it and moving down the list at the same time. so the list would read: tom smith bob brown steve simmons and sheet one after the list sheet would end up with 'tom smith' in cell ('c3') and the next sheet along would end up with bob brown in cell ('c3') etc.. I know I can use a For Each method here but I need a wee bit more help in phrasing it. Cheers H -- Hru48 ------------------------------------------------------------------------ Hru48's Profile: http://www.excelforum.com/member.php...o&userid=24895 View this thread: http://www.excelforum.com/showthread...hreadid=471460 |
#2
|
|||
|
|||
I've been thinking, maybe I could read the values into an array and then reproduce them on all the sheets like that? Am I way off track here? or maybe, If i put a matching function in all the sheets cells where i need to name and tried to match fill the gap that way.... this could work as on the list initial sheet i have the peoples name and in the cell to the right i have the names of the sheets which have been created to hold the names..... but how can i match something useing a sheet name? -- Hru48 ------------------------------------------------------------------------ Hru48's Profile: http://www.excelforum.com/member.php...o&userid=24895 View this thread: http://www.excelforum.com/showthread...hreadid=471460 |
#3
|
|||
|
|||
I did it a slightly different way- see the code that follows. Step 1
is to make a backup copy of your data so nothing important is lost. In the code that follows, you'll need to replace the word "Main" with the name of the tab that contains the list of names. Replace the word "Template" with the name of the tab that contains your template. This code makes a copy of Template for each person in the list; you have the option to rename each newly created template with the name of the person. The code requires that the cell immediately following the last name in the list is blank. Sub Hru48() Dim K As Byte, Inndex As Byte Dim Nayme As String Sheets("Main").Select 'This tab contains the list of names Range("c3").Select 'The first name in the list Do Until ActiveCell.Value = "" 'Assumes the cell after the last name in the list is blank Nayme = ActiveCell.Value Sheets("Template").Copy After:=Sheets(2) ' "Template" is the name of your template tab Inndex = ActiveSheet.Index Range("C3").Select ActiveCell.Formula = "=Main!C" & 3 + K ' Creates a formula that changes with each template copy to pick up the next name on the list ActiveSheet.Move After:=Sheets(Inndex + K) Sheets("Main").Select 'Return to pick up another name ActiveCell.Offset(1, 0).Select 'Get the next name K = K + 1 Loop End Sub |
#4
|
|||
|
|||
I think i may be doing something wrong.. when I run this I get an error: Run-time error 1004 Application defined or object defined error any advice? thanks -- Hru48 ------------------------------------------------------------------------ Hru48's Profile: http://www.excelforum.com/member.php...o&userid=24895 View this thread: http://www.excelforum.com/showthread...hreadid=471460 |
#5
|
|||
|
|||
If you have specific names you'd like to use for tab names that are
listed on the List sheet, the code I sent can be modified slightly to apply those names to the sheets. Let me know and I'll show you how. |
#6
|
|||
|
|||
Argh! The code ran for me- but we can get to the bottom of this. When
the code generates an error, you should get a window with then buttons Continue, End, Debug, and Help. Please run the code, and click Debug in that window. You should see a line of code highlighted in yellow. What line is highlighted, and where is the cell pointer? Are you in the UK? I notice the timestamps on our messages are out of order. |
#7
|
|||
|
|||
The code runs great now thanks alot, you have saved me many hours... but do you know how I can now adjust it a wee bit so it will name the new sheets it generates with a list of vales in column B which correspond with the names: So like the first sheet will be called a cos b1 is a and the second will be b cos b2 is b etc... Yeah, i'm in Northern Ireland although i'm fairly sure my body clock is running on american time ;) -- Hru48 ------------------------------------------------------------------------ Hru48's Profile: http://www.excelforum.com/member.php...o&userid=24895 View this thread: http://www.excelforum.com/showthread...hreadid=471460 |
#8
|
|||
|
|||
Hayley- Our messages have crossed, what with two separate posts. Do
you mind contacting me via email? The address is cyclezen AT yahoo DOT com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation lists - entering value not in list | Excel Discussion (Misc queries) | |||
Copying to an autofiltered list | Excel Discussion (Misc queries) | |||
Jump to the item in a list as the first letter is pressed | Excel Discussion (Misc queries) | |||
Making pick list conditional on selection from previous pick list | Excel Discussion (Misc queries) | |||
Selecting an Item from a List and getting a different item to pop. | Excel Worksheet Functions |