I need to insert tab name in cell A1 of every tab with changing tab names

Question

I need to open a worksheet with a fixed name and insert the name of each tab (which will change according to the current date) at the top of the sheet.

I modified some code from a previous answer and had the code working when it did not include the code to open the workbook. Now it flicks through the tabs but doesn't insert the name into Cells(1, 1) and I have no idea why. It also bugs at the end: Run-time error 91, which is less problematic but would be good to fix.

Any tips or advice much appreciated. Below is my current code:

Sub PSOPENTAB()
    ChDir "G:\directory"
    Workbooks.Open Filename:="G:\directory\filename.xls"
    Windows("filename.xls").Activate
    ActiveWorkbook.Worksheets(1).Activate
    Call nametop
End Sub

Sub nametop()
    Dim i As Long
    With ThisWorkbook
        'exit if Activesheet is the last tab
        If .ActiveSheet.Index + 1 > .Worksheets.Count Then
            Exit Sub
        End If
        For i = .ActiveSheet.Index To .Worksheets.Count - 1
            .ActiveSheet.Cells(1, 1) = .Worksheets(i).Name
             ActiveSheet.Next.Select
        Next i
    End With
End Sub

score 0 · Accepted Answer · edited May 23 '17 at 10:26

0

You need to reference your objects correctly.
Your problems are:

You use Thisworkbook in your nametop routine. So it will always work on the workbook containing the code.
You can change it to ActiveWorkbook but that may lead you to other problems in the future. See this cool stuff to know more about why to avoid Activeworkbook/Activesheet and the like

Applying what's discussed there, try below code:

Sub PSOPENTAB()
    Dim wb As Workbook
    Set wb = Workbooks.Open(Filename:="G:\directory\filename.xls")
    nametop wb
End Sub

Sub nametop(wb As Workbook)
    Dim ws As Worksheet
    For Each ws In wb.Worksheets
        ws.Cells(1, 1) = ws.Name
    Next ws
End Sub

Above code adds the name of the sheet in Cell A1 of every sheet.
Is this what you're trying?

edited May 23 '17 at 10:26

Community

1
1

answered Jan 20 '15 at 08:27

L42

19,427
11
44
68

Next i should be `Next ws` – chris neilsen Jan 20 '15 at 10:57
Hello! I changed it to Sub PSOPENTAB() Dim wb As Workbook Set wb = Workbooks.Open("G:\directory\filename.xls").Worksheets(1) nametop wb End Sub Sub nametop(wb As Workbook) Dim ws As Worksheet With wb For Each ws In wb.Worksheets ws.Cells(1, 1) = ws.Name Next i End With End Sub to get it to start on the first tab and to get rid of a couple of errors it was pulling, but now I'm getting the same issue (i.e. it's looping, I think, but not filling in the name in the cell) – Rachel Dear Jan 20 '15 at 14:48
Hi Chris - this returns a Invalid Next control variable reference, so I don't think it can be correct. – Rachel Dear Jan 20 '15 at 16:42
@RachelDear Can you illustrate how you're expected result looks like? – L42 Jan 21 '15 at 00:51
The idea above (adding the name of the sheet in Cell A1 of every sheet) is perfect, it's just not working. I'm going to try the code on a different spreadsheet in case it's a formatting issue. – Rachel Dear Jan 21 '15 at 06:59
@L42 I'm getting a type mismatch error here on Set wb = Workbooks.Open Filename:="G:\directory\filename.xls" – Rachel Dear Jan 21 '15 at 07:08
@L42, we have lift-off!! Superb. Many thanks for all your assistance. – Rachel Dear Jan 21 '15 at 07:18
@RachelDear Np. Glad we did. Btw, see [accepting answers](http://stackoverflow.com/help/someone-answers) as one way of saying thanks in SO. It will also improve your reputation and will attract more people answering your post. Welcome to SO. :-) – L42 Jan 21 '15 at 07:20

I need to insert tab name in cell A1 of every tab with changing tab names

1 Answers1