Copying list item (a bit less vague)

H

Hru48

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
 
H

Hru48

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? :confused:


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?
 
D

Dave O

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
 
H

Hru48

I think i may be doing something wrong.. when I run this I get a
error:

Run-time error 1004

Application defined or object defined error



any advice?

thank
 
D

Dave O

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.
 
D

Dave O

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.
 
H

Hru48

The code runs great now thanks alot, you have saved me many hours... bu
do you know how I can now adjust it a wee bit so it will name the ne
sheets it generates with a list of vales in column B which correspon
with the names:

So like the first sheet will be called a cos b1 is a and the secon
will be b cos b2 is b etc...


Yeah, i'm in Northern Ireland although i'm fairly sure my body clock i
running on american time ;
 
D

Dave O

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
 
Top