Macro to create multiple sheet, copy certain values and loop until end of row data
Hi, guys! I news help on this one. I would like to create a macro to copy sheet, name it based on cells from another sheet. I managed, through research, to execute it through looping. see code below:
It worked well, however, I also need to link or copy values corresponding to each name before it stops executing e.g. Name, Position from Sheet1. I would appreciate any help. Thanks!
Please Login or Register to view this content.
Last edited by jhoelski; 02-17-2012 at 07:29 AM. Reason: Kudos to Rory!
Please add code tags to your code in accordance with forum rules.
Good luck.
Sorry for that I'm just a newbie here. Anyway, here is the code:
Please Login or Register to view this content.
Last edited by jhoelski; 02-16-2012 at 08:35 AM. Reason: Abidance with forum rules
Could you clarify what you mean by this?Originally Posted by jhoelski
Well, i have two sheets, sheet1 contains the names, sheet2 is the one that will be created by the macro many times and then rename it based on sheet1 row data starting from A1 until blank, hence the loop. However, I also need to copy values on sheet1, particularly column b1 & c1 to each worksheet. My data is like these:
Sheet1
A. B. C.
1 Name1 rank office
2 Name 2 rank office
3 Name 3 rank office
sheet2 (the one to be copied)
Has cells for name, rank & office
When the macro runs, it creates worksheets for each name listed. What i need is to add a block of code that will also copy the name, rank & office. thanks!
I think you need something like this:
Please Login or Register to view this content.
I tested the code, it doesn't seem to be working. No error, just not executing as it is supposed to. Help! Thanks!
I need more to go on than that.
Now running but output is not as desired, I appreciate it though. Rory, can you tell me how this code works?! I mean, can you elaborate what it is supposed to do? Enlighten me on this one, perhaps this would do the trick. Thanks!Please Login or Register to view this content.
It should populate range A1:C1 on the new sheet with the name of the sheet (from Sheet1) and the two cells to the right of the name.
Okay. What if I need to select the following range C:14 to E14?! They are three cells, adjacent cells I need to auto-populate, to the right of B14 from which the name of the worksheet, created by macro, is derived. Thanks again, Rory.
Assuming you still want to populate A1:C1 on each sheet
Please Login or Register to view this content.
Got it,Rory! You've been a big help! One more request, what if aside from the autopopulate there is a bunch formula as well which are linked to the autopopulated sheets made by the macro, they are on absolute reference. Let me make it clear:
A. B. C. D. E.
1 name rank office =sheet2!b$4$ =sheet2!b$6$
2 ..
3 ..
The macro you gave me really saved me. I would appreciate it if you could help me further. Thanks again, Rory.
Last edited by jhoelski; 02-16-2012 at 11:43 PM.
Let me summarize what the macro intends to do:
1. Create copy of sheet2 based on names on sheet1
2. Rename the sheets accordingly
3. Auto-populate certain values (name, etc.)
4. Create formula or absolute references to each named wsheet (on sheet1, adjacent to the name) before it proceeds with creating another worsheet with next name (loop until EOF)
thanks for all the help!
Last edited by jhoelski; 02-17-2012 at 12:00 AM.
Try this:
Please Login or Register to view this content.
Hi, Rory. I appreciate the help you have extended so far, kindly refer to the attachment. I've included the sample worksheet I am working on hoping you could decipher what I need to do, thanks again!
Would have saved time to have that to start with...
See attached.
Wow, that was fast! Bravo to you! Tested it and worked!Thanks a bunch, Rory!
One more request, Rory, pleassseeee.....Could you auto-populate the formula column as well?! I mean automate it instead of manually typing in the INDEX, MATCH formula for each individual name? Thanks, man!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks