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
Bookmarks