Chookz
Board Regular
- Joined
- May 9, 2011
- Messages
- 95
Hey guys,
I have a VBA code that works on a single sheet, but gets an error when I try to run it across numerous sheets.
The following code works on a single sheet:
I now want to run the code on numerous sheets that meet a criteria, whilst excluding some sheets (It also adds some graphs at the end, but that is working fine).
The new code is now:
This code works for the first sheet in the range, but when it gets to the second sheet I get the following error:
Run-time error '438':
Object doesn't support this property or method
Clicking debug, the error is occuring here:
Any idea as to how to fix it? It's very frustrating as when I run the first code on this second worksheet, this paste special command works fine...
Thanks in advance guys
I have a VBA code that works on a single sheet, but gets an error when I try to run it across numerous sheets.
The following code works on a single sheet:
Code:
Sub Macro1()
Dim newRow As Integer
Dim firstCol As Integer
Range("T2").Select
Do While ActiveCell.Value <> ""
ActiveCell.Offset(1, 0).Select
Loop
newRow = ActiveCell.Row
firstCol = ActiveCell.Column
'Week Beginning
ActiveCell.Formula = "=VLOOKUP(A3, WeekBeginning2013, 2, FALSE)"
'Team
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1], Team2013, 4, FALSE), """")"
'Round
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-2], Round2013, 5, FALSE), """")"
'Time
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=IFERROR(GETPIVOTDATA(""Average of Field Time"",'PT1'!$A$1,""Player Name"",$A$1,""Period Name"",""Session"",""Round"",$A$5, ""Team"",$A$4), """")"
'Distance
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=IFERROR(GETPIVOTDATA(""Average of Odometer"",'PT1'!$A$1,""Player Name"",$A$1,""Period Name"",""Session"",""Round"",$A$5, ""Team"",$A$4), """")"
'Threshold Distance
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=IFERROR(GETPIVOTDATA(""Average of Threshold Dist"",'PT1'!$A$1,""Player Name"",$A$1,""Period Name"",""Session"",""Round"",$A$5, ""Team"",$A$4), """")"
'Threshold %
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=IFERROR(GETPIVOTDATA(""Average of Threshold %"",'PT1'!$A$1,""Player Name"",$A$1,""Period Name"",""Session"",""Round"",$A$5, ""Team"",$A$4), """")"
'Work Rate (m/min)
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=IFERROR(GETPIVOTDATA(""Average of Meterage / min"",'PT1'!$A$1,""Player Name"",$A$1,""Period Name"",""Session"",""Round"",$A$5, ""Team"",$A$4), """")"
'Z5 Efforts
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=IFERROR(GETPIVOTDATA(""Average of Vel Zone 5 Efforts"",'PT1'!$A$1,""Player Name"",$A$1,""Period Name"",""Session"",""Round"",$A$5, ""Team"",$A$4), """")"
'Z6 Efforts
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=IFERROR(GETPIVOTDATA(""Average of Vel Zone 6 Efforts"",'PT1'!$A$1,""Player Name"",$A$1,""Period Name"",""Session"",""Round"",$A$5, ""Team"",$A$4), """")"
'Z6 Distance
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=IFERROR(GETPIVOTDATA(""Average of Vel Zone 6 Dist"",'PT1'!$A$1,""Player Name"",$A$1,""Period Name"",""Session"",""Round"",$A$5, ""Team"",$A$4), """")"
'Av. Z6 Effort Dist.
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=IFERROR(GETPIVOTDATA(""Average of Vel Zone 6 Avg Effort Dist"",'PT1'!$A$1,""Player Name"",$A$1,""Period Name"",""Session"",""Round"",$A$5, ""Team"",$A$4), """")"
'Top Speed
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=IFERROR(GETPIVOTDATA(""Average of Max Velocity"",'PT1'!$A$1,""Player Name"",$A$1,""Period Name"",""Session"",""Round"",$A$5, ""Team"",$A$4), """")"
'Accel
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=IFERROR(GETPIVOTDATA(""Average of IMA Accel High"",'PT1'!$A$1,""Player Name"",$A$1,""Period Name"",""Session"",""Round"",$A$5, ""Team"",$A$4), """")"
'Decel
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=IFERROR(GETPIVOTDATA(""Average of IMA Decel High"",'PT1'!$A$1,""Player Name"",$A$1,""Period Name"",""Session"",""Round"",$A$5, ""Team"",$A$4), """")"
'COD Left
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=IFERROR(GETPIVOTDATA(""Average of IMA COD Left High"",'PT1'!$A$1,""Player Name"",$A$1,""Period Name"",""Session"",""Round"",$A$5, ""Team"",$A$4), """")"
'COD Right
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=IFERROR(GETPIVOTDATA(""Average of IMA COD Right High"",'PT1'!$A$1,""Player Name"",$A$1,""Period Name"",""Session"",""Round"",$A$5, ""Team"",$A$4), """")"
'High Intensity Movements
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=IFERROR(GETPIVOTDATA(""Average of High Intensity Movements"",'PT1'!$A$1,""Player Name"",$A$1,""Period Name"",""Session"",""Round"",$A$5, ""Team"",$A$4), """")"
'Player Load
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=IFERROR(GETPIVOTDATA(""Average of Player Load"",'PT1'!$A$1,""Player Name"",$A$1,""Period Name"",""Session"",""Round"",$A$5, ""Team"",$A$4), """")"
'Player Load/m (x 1000)
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=IFERROR(GETPIVOTDATA(""Average of Player Load / m (x1000)"",'PT1'!$A$1,""Player Name"",$A$1,""Period Name"",""Session"",""Round"",$A$5, ""Team"",$A$4), """")"
'Round Name
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=IFERROR(CONCATENATE(RC[-19], "" "", RC[-18]), """")"
Range(Cells(newRow, firstCol), ActiveCell).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub
I now want to run the code on numerous sheets that meet a criteria, whilst excluding some sheets (It also adds some graphs at the end, but that is working fine).
The new code is now:
Code:
Sub MatchGraphs()
Dim ws As Worksheet
Dim newRow As Integer
Dim firstCol As Integer
Dim CHARTDATA As Range
Dim XDATA As Range
Dim CHARTDATA2 As Range
Dim XDATA2 As Range
For Each ws In Worksheets
If ws.Range("B11").Value <> "" And _
ws.Name <> "RawData" And _
ws.Name <> "PT1" And _
ws.Name <> "PT2" And _
ws.Name <> "PT3" And _
ws.Name <> "Top 5" And _
ws.Name <> "Top Perf" And _
ws.Name <> "Summary" And _
ws.Name <> "Lookups" Then
ws.Activate
Range("T2").Select
Do While ActiveCell.Value <> ""
ActiveCell.Offset(1, 0).Select
Loop
newRow = ActiveCell.Row
firstCol = ActiveCell.Column
'Week Beginning
ActiveCell.Formula = "=VLOOKUP(A3, WeekBeginning2013, 2, FALSE)"
'Team
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1], Team2013, 4, FALSE), """")"
'Round
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-2], Round2013, 5, FALSE), """")"
'Time
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=IFERROR(GETPIVOTDATA(""Average of Field Time"",'PT1'!$A$1,""Player Name"",$A$1,""Period Name"",""Session"",""Round"",$A$5, ""Team"",$A$4), """")"
'Distance
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=IFERROR(GETPIVOTDATA(""Average of Odometer"",'PT1'!$A$1,""Player Name"",$A$1,""Period Name"",""Session"",""Round"",$A$5, ""Team"",$A$4), """")"
'Threshold Distance
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=IFERROR(GETPIVOTDATA(""Average of Threshold Dist"",'PT1'!$A$1,""Player Name"",$A$1,""Period Name"",""Session"",""Round"",$A$5, ""Team"",$A$4), """")"
'Threshold %
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=IFERROR(GETPIVOTDATA(""Average of Threshold %"",'PT1'!$A$1,""Player Name"",$A$1,""Period Name"",""Session"",""Round"",$A$5, ""Team"",$A$4), """")"
'Work Rate (m/min)
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=IFERROR(GETPIVOTDATA(""Average of Meterage / min"",'PT1'!$A$1,""Player Name"",$A$1,""Period Name"",""Session"",""Round"",$A$5, ""Team"",$A$4), """")"
'Z5 Efforts
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=IFERROR(GETPIVOTDATA(""Average of Vel Zone 5 Efforts"",'PT1'!$A$1,""Player Name"",$A$1,""Period Name"",""Session"",""Round"",$A$5, ""Team"",$A$4), """")"
'Z6 Efforts
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=IFERROR(GETPIVOTDATA(""Average of Vel Zone 6 Efforts"",'PT1'!$A$1,""Player Name"",$A$1,""Period Name"",""Session"",""Round"",$A$5, ""Team"",$A$4), """")"
'Z6 Distance
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=IFERROR(GETPIVOTDATA(""Average of Vel Zone 6 Dist"",'PT1'!$A$1,""Player Name"",$A$1,""Period Name"",""Session"",""Round"",$A$5, ""Team"",$A$4), """")"
'Av. Z6 Effort Dist.
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=IFERROR(GETPIVOTDATA(""Average of Vel Zone 6 Avg Effort Dist"",'PT1'!$A$1,""Player Name"",$A$1,""Period Name"",""Session"",""Round"",$A$5, ""Team"",$A$4), """")"
'Top Speed
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=IFERROR(GETPIVOTDATA(""Average of Max Velocity"",'PT1'!$A$1,""Player Name"",$A$1,""Period Name"",""Session"",""Round"",$A$5, ""Team"",$A$4), """")"
'Accel
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=IFERROR(GETPIVOTDATA(""Average of IMA Accel High"",'PT1'!$A$1,""Player Name"",$A$1,""Period Name"",""Session"",""Round"",$A$5, ""Team"",$A$4), """")"
'Decel
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=IFERROR(GETPIVOTDATA(""Average of IMA Decel High"",'PT1'!$A$1,""Player Name"",$A$1,""Period Name"",""Session"",""Round"",$A$5, ""Team"",$A$4), """")"
'COD Left
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=IFERROR(GETPIVOTDATA(""Average of IMA COD Left High"",'PT1'!$A$1,""Player Name"",$A$1,""Period Name"",""Session"",""Round"",$A$5, ""Team"",$A$4), """")"
'COD Right
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=IFERROR(GETPIVOTDATA(""Average of IMA COD Right High"",'PT1'!$A$1,""Player Name"",$A$1,""Period Name"",""Session"",""Round"",$A$5, ""Team"",$A$4), """")"
'High Intensity Movements
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=IFERROR(GETPIVOTDATA(""Average of High Intensity Movements"",'PT1'!$A$1,""Player Name"",$A$1,""Period Name"",""Session"",""Round"",$A$5, ""Team"",$A$4), """")"
'Player Load
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=IFERROR(GETPIVOTDATA(""Average of Player Load"",'PT1'!$A$1,""Player Name"",$A$1,""Period Name"",""Session"",""Round"",$A$5, ""Team"",$A$4), """")"
'Player Load/m (x 1000)
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=IFERROR(GETPIVOTDATA(""Average of Player Load / m (x1000)"",'PT1'!$A$1,""Player Name"",$A$1,""Period Name"",""Session"",""Round"",$A$5, ""Team"",$A$4), """")"
'Round Name
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=IFERROR(CONCATENATE(RC[-19], "" "", RC[-18]), """")"
Range(Cells(newRow, firstCol), ActiveCell).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Set CHARTDATA = Range(Range("X2"), Range("X" & newRow))
Set XDATA = Range(Range("AN2"), Range("AN" & newRow))
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.SeriesCollection(1).Values = CHARTDATA
ActiveChart.SeriesCollection(1).XValues = XDATA
Set CHARTDATA2 = Range(Range("Y2"), Range("Y" & newRow))
Set XDATA2 = Range(Range("AN2"), Range("AN" & newRow))
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.SeriesCollection(1).Values = CHARTDATA2
ActiveChart.SeriesCollection(1).XValues = XDATA2
Set CHARTDATA = Nothing
Set XDATA = Nothing
Set CHARTDATA2 = Nothing
Set XDATA2 = Nothing
End If
Next ws
End Sub
This code works for the first sheet in the range, but when it gets to the second sheet I get the following error:
Run-time error '438':
Object doesn't support this property or method
Clicking debug, the error is occuring here:
Code:
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Any idea as to how to fix it? It's very frustrating as when I run the first code on this second worksheet, this paste special command works fine...
Thanks in advance guys