Trying to make a recorded macro work properly

Hello everyone

Although I've been a member for a while, this is my first post.

This is my problem. I have recorded a macro to pull data from an Excel worksheet into a pivot table on a new worksheet with defined formatting and layout.
I also want the macro to rename the tabs of the data sheet and pivot table sheet. I want the macro to be able to be run no matter what the data sheet name be, "Sheet1", "Data", etc, it's only the 'new' name that is important, in this instance I want them to be titled, "Labour Hours" for the data sheet and "Hours Pivot" for the pivot.

I have seen many comments here about wrapping code into tags, but I have no idea what that is so rather than make the same error, can someone tell me how to upload my recorded code. I have attached the data sheet to begin with.

Any help you can give would be hugely appreciated.

Many thanks in advance.

Nick

Hi arlu

Thank you very much for your guidance, here is the code I need help with ...

Sub Labour_Hours()
'
' Labour_Hours Macro
' abour actuals into pivot pre determined layout
' Recorded by Nicholas Owen 04-Dec-12

'
    Sheets("ActiveSheet").Name = "Labour Hours"
    Columns("Y:Y").Select
    Selection.Insert Shift:=xlToRight
    Range("Y1").Select
    ActiveCell.FormulaR1C1 = "Total Hours"
    With ActiveCell.Characters(Start:=1, Length:=11).Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Range("Y2").Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[-2],RC[-1])"
    Range("Y2").Select
    Selection.AutoFill Destination:=Range("Y2:Y2596"), Type:=xlFillDefault
    Range("Y2:Y2596").Select
    Rows("1:1").Select
    Selection.Font.Bold = True
    Cells.Select
    Cells.EntireColumn.AutoFit
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Labour Hours!R1C1:R1048576C34", Version:=xlPivotTableVersion12). _
        CreatePivotTable TableDestination:="Sheet2!R3C1", TableName:="PivotTable1" _
        , DefaultVersion:=xlPivotTableVersion12
    Sheets("Sheet2").Select
    Cells(3, 1).Select
    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "Hours Pivot"
    Range("A20").Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Staff Name")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Cost Code")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("G/L Date")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Hours"), "Count of Total Hours", xlCount
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Total Hours")
        .Caption = "Sum of Total Hours"
        .Function = xlSum
    End With
    Range("B4").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Rows("4:4").Select
    Range("XEF4").Activate
    Selection.NumberFormat = "d-mmm-yy"
    Range("B5").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Style = "Comma"
    Range("Q5").Select
End Sub

Hi Arlette

During my search for a fix I have seen that this recording is far from efficient, so in answer to your question, both please.

My original error was a "Run Time Error 9: Subscript out of range" which in my very limited knowledge I thought is due to it was referencing the name of the sheet in which I created the macro.

I suppose I have a number of issues;

next to no knowledge of macros
I want the macro to run in the active worksheet no matter what it's named
I want the macro to rename the data sheet and pivot sheet as per my original post
I would like to be as efficient as possible so I can learn from it for the future
I don't know where to start

Thanks to all who contributed here. I eventually solved this through trial and error, and referencing lots of www sites, so thanks to all the contributors and sources out there; I couldn't have done it without you.