How to create macro of fill in cells with Active Sheet Name

Cyphas · Jul 8, 2015

I tried writing a macro to use the name of the worksheet to populate a column in several documents but it only works in the one I recorded the macro for but not others.

I know I can use ActiveSheet.Name to return the active worksheet. However, i am at a lost in coding to get it to past the name into current column on other documents

Code:

Sub Fill_in_FieldCol_withActive_Sheet_Name()
'
' Fill_in_FieldCol_withActive_Sheet_Name Macro
' Inserts a Column at specific location in Active Worksheet. Populate that Column with Active Worksheet Name.
'
' Keyboard Shortcut: Ctrl+Shift+P
'
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Location"
    Range("C2").Select
    Sheets("Builders in Barrow-in-furness").Select
    Sheets("Builders in Barrow-in-furness").Name = "Builders in Barrow-in-furness"
    Range("C2").Select
    ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
        DisplayAsIcon:=False, NoHTMLFormatting:=True
    Selection.AutoFill Destination:=Range("C2:C42"), Type:=xlFillDefault
    Range("C2:C42").Select
    ActiveWorkbook.Save
End Sub

Also, is there a way to get this code to open all files in a particular folder and run Macro on all without me having to open them individually and executing?

I also tried this but go errors

Code:

Sub Fill_in_FieldCol_withActive_Sheet_Name()'
' Fill_in_FieldCol_withActive_Sheet_Name Macro
' Inserts a Column at specific location in Active Worksheet. Populate that Column with Active Worksheet Name.
'
' Keyboard Shortcut: Ctrl+Shift+P
'
        
    shName = ActiveSheet.Name
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Location"
'   Sheets("Builders in Barrow-in-furness").Select
'   Sheets("Builders in Barrow-in-furness").Name = "Builders in Barrow-in-furness"
    Sheets(ActiveSheet.Name) = shName
    Range("C2").Select
    ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
        DisplayAsIcon:=False, NoHTMLFormatting:=True
    Selection.AutoFill Destination:=Range("C2:C42"), Type:=xlFillDefault
    Range("C2:C42").Select
    ActiveWorkbook.Save
End Sub

Thanks
Dev

How to create macro of fill in cells with Active Sheet Name

Cyphas

New member