Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Hru48
 
Posts: n/a
Default 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   Report Post  
Hru48
 
Posts: n/a
Default


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   Report Post  
Dave O
 
Posts: n/a
Default

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   Report Post  
Hru48
 
Posts: n/a
Default


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   Report Post  
Dave O
 
Posts: n/a
Default

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   Report Post  
Dave O
 
Posts: n/a
Default

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   Report Post  
Hru48
 
Posts: n/a
Default


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   Report Post  
Dave O
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Data Validation lists - entering value not in list rgarber50 Excel Discussion (Misc queries) 3 July 19th 05 08:35 PM
Copying to an autofiltered list Phil Excel Discussion (Misc queries) 3 June 30th 05 02:08 PM
Jump to the item in a list as the first letter is pressed Kiran Excel Discussion (Misc queries) 2 June 29th 05 10:25 AM
Making pick list conditional on selection from previous pick list Stewart Excel Discussion (Misc queries) 1 June 27th 05 11:30 AM
Selecting an Item from a List and getting a different item to pop. Matt Excel Worksheet Functions 1 December 7th 04 03:37 PM


All times are GMT +1. The time now is 07:29 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"