Hello All,
I have a workbook that contains worksheets of the same structure and I need to update them monthly with new data.
I used to record macros in Excel 2010 and then run them on the other worksheets with the same structure as the one I recorded the macro on. I am trying to do the same in Excel 2013 but I have issues with it, I get run-time error `1004` (select method of Range class failed). When I click on debug, it highlights the following part: Range("Table1016242934384261222[@Column19]").Select
(Full code is below.)
I have never had this issue in Excel 2010, actually I can`t even remember seeing these long table names consisting of numbers in the codes in Excel 2010.
I also seem to have issues with the relative references in my code.
What could be the problem?
Cheers

Sub z()
'
' z Macro
'

'
    Columns("Q:Q").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("O18").Select
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    Range("P2").Select
    Selection.Copy
    Range("Q2").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("Table1016242934384261222[@Column19]").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("P5").Select
    Selection.Copy
    Range("Q5").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("P6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("Q6").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("Q2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "='X:\Corporate\Category Management\Accounts\K&D\Scorecards&charts\[K&D Scorecards flat.xlsx]Total'!R[61]C2"
    Range("Q3").Select
    ActiveCell.FormulaR1C1 = _
        "='X:\Corporate\Category Management\Accounts\K&D\Scorecards&charts\[K&D Scorecards flat.xlsx]Total'!R[61]C2"
    Range("Q4").Select
    ActiveCell.FormulaR1C1 = _
        "='X:\Corporate\Category Management\Accounts\K&D\Scorecards&charts\[K&D Scorecards flat.xlsx]Total'!R[61]C2"
    Range("Q5").Select
    ActiveCell.FormulaR1C1 = _
        "='X:\Corporate\Category Management\Accounts\K&D\Scorecards&charts\[K&D Scorecards flat.xlsx]Total'!R[61]C2"
    Range("Q6").Select
    ActiveCell.FormulaR1C1 = _
        "='X:\Corporate\Category Management\Accounts\K&D\Scorecards&charts\[K&D Scorecards flat.xlsx]Total'!R[63]C2"
    Range("Q2").Select
    ActiveCell.FormulaR1C1 = _
        "='X:\Corporate\Category Management\Accounts\K&D\Scorecards&charts\[K&D Scorecards flat.xlsx]Total'!R[61]C2"
    Range("Q2").Select
    Selection.Copy
    Range("R2").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("Q3").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("Table1016242934384261222[@Column18]").Select
    Selection.ClearContents
    Range("Q5").Select
    Selection.Copy
    Range("R5").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("Q6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("R6").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("R2:R6").Select
    Selection.Replace What:="$B", Replacement:="$C", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("Table1016242934384261222[@Column19]").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Jan-2015"
    Range("Table1016242934384261222[@Column18]").Select
    ActiveCell.FormulaR1C1 = "Feb-2015"
    Range("R4").Select
End Sub