Create Copy of Worksheet and have Cell c2 = tab name of original tab used to create new tab

[pjn560]

I need to create a new tab from the left most tab in workbook and have it placed to the left of the tab I am copying. Then in new tab have cell C2 = the name of the tab I used to create the new tab. A formula would be best but using code will work.

 

[cerfani]

a formula to create a new tab... dont think so but you can use vba...

name = Range("C2").Value
Set s = Sheets.Add Before:=Sheets(1)
s.Name = name

what's your question?

 

[Norie]

Perhaps.

Sheets(1).Copy Before:=Sheets(1)

Sheets(1).Range("C2").Value = Sheets(2).Name

 

[pjn560]

In my spreadsheet I start with tab "Feb 2018", I need to create a new tab (will be renamed "Mar 2018") from this tab and need Cell C2 to be Feb 2018. Next month I will create new tab from the "Mar 2018" tab and rename it "Apr 2018" and need Cell C2 to be Mar 2018. The new tab will always be to the left of the tab used to create the new one.

 

[Norie]

The code I posted will copy the leftmost sheet and place the copy in the leftmost position, it will then put the name of the sheet that was copied into C2 on the new sheet.

 

[pjn560]

Thanks Norie, this worked great!!!