Results 1 to 1 of 1

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

  1. #1
    Neophyte Cyphas's Avatar
    Join Date
    Jul 2015
    Location
    UK
    Posts
    1
    Articles
    0

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Last edited by Cyphas; 2015-07-08 at 01:06 PM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •