1

I have a workbook contain about 50 worksheets (sheet 1, sheet 2, sheet 3,........, sheet 50). I want to get the sheet name as a column infront of my data in each sheet. I used following code for that.

Sub tgr1()
    Dim ws As Worksheet
    Dim wsDest As Worksheet

    Set wsDest = Sheets("Sheet1")

    For Each ws In ActiveWorkbook.Sheets
        If ws.Name <> wsDest.Name Then
            ws.Range("A:A").Insert Shift:=xlToRight
            'Selection.Insert Shift:=xlToRight
            ws.Range("A12").FormulaR1C1 = _
            "=IF(RC[1]>0,MID(CELL(""filename"",R[-11]C[1]),FIND(""]"",CELL(""filename"",R[-11]C[1]))+1,255),"""")"
            ws.Range("A12").Copy
            ws.Range("A13:A500").PasteSpecial xlPasteFormulas
            ws.Range("A12:A500").Copy
            ws.Range("A12:A500").PasteSpecial xlPasteValues
        End If
        ActiveWorkbook.Save
    Next ws

But this code isn't working for all the sheets i have. it applies to random sheets. What should i do to make it apply for all the sheets.

Vityata
  • 42,633
  • 8
  • 55
  • 100
Samuu
  • 17
  • 5

1 Answers1

0

1. Change ActiveWorkbook to ThisWorkbook

2. To get the worksheet name all you need is ws.Range("A12").Value = Ws.Name

3. No point saving the workbook each time the loop runs. Do it outside the loop.

Is this what you are trying?

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim wsDest As Worksheet

    Set wsDest = Sheets("Sheet1")

    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> wsDest.Name Then
            ws.Columns(1).Insert Shift:=xlToRight
            ws.Range("A12:A500").Value = ws.Name
        End If
    Next ws
    
    ThisWorkbook.Save
    DoEvents
End Sub
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • Thank you for your answer. Now it applies to all the sheets(Solved the issue i had with random sheets). But now sheet name only appears in A12 cell. I need to repeat it for each and every rows which contain data in the sheet. (sheets contain different number of rows, eg-50,60,..,etc ) – Samuu Oct 18 '20 at 07:50
  • All the data i have is in 12th row and below – Samuu Oct 18 '20 at 08:02
  • You need to refresh the page. I had already made that edit ;) – Siddharth Rout Oct 18 '20 at 08:21
  • BTW if there are going to be different number of rows then you might want to find the last row as shown [Here](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba/11169920#11169920) and then do `ws.Range("A12:A" & LastRow).Value = ws.Name` – Siddharth Rout Oct 18 '20 at 08:52