Menu

Populate data from a column in one worksheet to a cell in multiple other sheets [Solved]

Report
-
 Vee1893 -
In a workbook, I want to be able to copy all the cells in column B of sheet "Raw Data" into multiple copies of an existing template sheet corresponding to each value. (note: not all cells have values).

Column B in the "Raw Data" sheet has about 800 cells/values. I want the workbook to be able to:

1. Pick up the 1st value from column B from the "Raw Data" sheet.
2. Create a copy of the existing "Template" sheet where this value will be pasted in cell B5.
3. Change the name of the copied template sheet to the value that was entered in cell B5.
4. Then continue steps 1-3 starting from the next number till it reaches the end of column B.
See more

2 replies

Answer 1 / 2
Posts
2591
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 11, 2020
 396
Hi Vee,

Since I don't think the provided links will guide you to your answer, you can try the following code: 
Sub RunMe()
Dim shName As String

Sheets("Raw Data").Select

For Each cell In Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row)
    If cell.Value = vbNullString Then GoTo NextCell
    shName = cell.Value
    Sheets("Template").Copy before:=Sheets("Template")
    ActiveSheet.Name = shName
    Range("B5").Value = shName
NextCell:
Next cell
End Sub


Best regards,
Trowa
Comment
Thank you
3
Thank you

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 4334 users have said thank you to us this month

Report
David Webb
Posts
553
Registration date
Monday November 25, 2019
Status
Administrator
Last seen
February 13, 2020
 505
Thanks Trowa!
Report

Thank you Trowa. This really helped!
Answer 2 / 2
Posts
553
Registration date
Monday November 25, 2019
Status
Administrator
Last seen
February 13, 2020
 505
Hello there,

Have a look at this forum answer on auto population data from multiple sheets that could help you out.

Or check out this article on how to populate multiple sheets based on data entered.

Hope this helps,
David

Reply Ask a question