How to create multiple sheets of one sheet & renaming new sheets based on Column name of master sheet
Hi,
I would like create multiple sheets of one sheet & rename new sheets based on Column name of master sheet. I am using below 2 codes But I am not sure how can i combine these 2 codes. The below code creates multiple sheets for me but i would like to rename new sheets created based upon master column sheets name in same order of column.
ActiveSheet.Name = Sheet1.cell(1,5)
thereafter
ActiveSheet.Name = Sheet1.cell(1,6) & so On
Sub Copysheet()
Dim i As Integer
Dim p As Integer
On Error GoTo out
i = InputBox("How many copies do you what?", "Making Copies")
Application.ScreenUpdating = False
p = 0
Do
ActiveSheet.Copy After:=Sheets(Sheets.Count)
p = p + 1
Loop Until p = i
Application.ScreenUpdating = True
Exit Sub
out:
MsgBox "copy was cancelled"
Application.ScreenUpdating = True
End Sub
Copy & rename worksheets
You're almost there. Just add a line of code to rename the sheet after adding it within your Do loop, as below...
* Sheets(Sheets.Count).Name = Sheet1.Cells(1, 5 + p).Value
Sub Copysheet()
Dim i As Integer
Dim p As Integer
On Error GoTo out
i = InputBox("How many copies do you what?", "Making Copies")
Application.ScreenUpdating = False
p = 0
Do
ActiveSheet.Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = Sheet1.Cells(1, 5 + p).Value
p = p + 1
Loop Until p = i
Application.ScreenUpdating = True
Exit Sub
out:
MsgBox "copy was cancelled"
Application.ScreenUpdating = True
End Sub