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.
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
- Posts
-
1711
- Registration date
- Monday November 25, 2019
- Status
- Administrator
- Last seen
- July 29, 2020
2,346