Macro to Rename worksheet tabs

[Sallyprit]

Hi I copied the following code from a post on this site which works beautifully. I now need to rename each new tab to cell B2 of that new worksheet. If anyone can help I'd really appreciate it. Thanks Sally
Sub copycols()
Dim LC As Long, i As Long, ws As Worksheet
With ActiveSheet
LC = .Cells(1, Columns.Count).End(xlToLeft).Column
For i = 2 To LC
Set ws = Sheets.Add(after:=Sheets(Sheets.Count))
.Columns(1).Copy Destination:=ws.Range("A1")
.Columns(i).Copy Destination:=ws.Range("B1")
Next i
End With
End Sub

 

[Joe4]

Welcome to the Board!

Try inserting the line in red

Set ws = Sheets.Add(after:=Sheets(Sheets.Count))
ws.Name = ws.Range("B2")

 

[Sallyprit]

Welcome to the Board!

Try inserting the line in red

Set ws = Sheets.Add(after:=Sheets(Sheets.Count))
ws.Name = ws.Range("B2")

Thanks Joe so I've made the code as below bur I now get a run time error. Have I put the red line in the wrongplace maybe? Really appreciate your help.

Sub copycols()
Dim LC As Long, i As Long, ws As Worksheet
With ActiveSheet
LC = .Cells(1, Columns.Count).End(xlToLeft).Column
For i = 2 To LC
Set ws = Sheets.Add(after:=Sheets(Sheets.Count))
ws.Name = ws.Range("B2")
.Columns(1).Copy Destination:=ws.Range("A1")
.Columns(i).Copy Destination:=ws.Range("B1")
Next i
End With
End Sub

 

[Joe4]

Whoops, I just noticed that the two rows of code below that are what is populating those columns. So it probably needs to be after them, i.e.

.Columns(1).Copy Destination:=ws.Range("A1")
.Columns(i).Copy Destination:=ws.Range("B1")
ws.Name = ws.Range("B2")

Sorry about that!

 

[Sallyprit]

Thanks Joe so I've made the code as below bur I now get a run time error. Have I put the red line in the wrongplace maybe? Really appreciate your help.

Sub copycols()
Dim LC As Long, i As Long, ws As Worksheet
With ActiveSheet
LC = .Cells(1, Columns.Count).End(xlToLeft).Column
For i = 2 To LC
Set ws = Sheets.Add(after:=Sheets(Sheets.Count))
ws.Name = ws.Range("B2")
.Columns(1).Copy Destination:=ws.Range("A1")
.Columns(i).Copy Destination:=ws.Range("B1")
Next i
End With
End Sub

View attachment 105697

I have now moved the red code down to make it

Sub copycols()
Dim LC As Long, i As Long, ws As Worksheet
With ActiveSheet
LC = .Cells(1, Columns.Count).End(xlToLeft).Column
For i = 2 To LC
Set ws = Sheets.Add(after:=Sheets(Sheets.Count))
.Columns(1).Copy Destination:=ws.Range("A1")
.Columns(i).Copy Destination:=ws.Range("B1")
ws.Name = ws.Range("B1")
Next i
End With
End Sub

it works perfectly, thank you.

 

[Sallyprit]

Whoops, I just noticed that the two rows of code below that are what is populating those columns. So it probably needs to be after them, i.e.

.Columns(1).Copy Destination:=ws.Range("A1")
.Columns(i).Copy Destination:=ws.Range("B1")
ws.Name = ws.Range("B2")

Sorry about that!

Its no problem at all. I managed to work it out!

 

[Joe4]

You are welcome!
Glad I was able to help.

Note when marking a post as the solution, please mark the original post that contains the working solution (and not you own post acknowledging that another post was the solution).
I have updated it for you on this thread.