Please help with VBA code - Run-time error '438': Object doesn't support this property or method

Chookz

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:

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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
Sorry for the shameless bump, but this forum is so popular this thread has already gone back 15 + pages!
Any VBA gurus able to help with this problem?
 
Upvote 0
Hi

At the time of the error what are the values of :-
1, newRow
2, firstCol and
3, the address of ActiveCell
 
Upvote 0
ukmikeb said:
Hi

At the time of the error what are the values of :-
1, newRow
2, firstCol and
3, the address of ActiveCell
Click to expand...

Hey ukmikeb

newRow & firstCol point to the first cell from all the GETPIVOTDATA calculations. In this case cell T2 which is a date - 13/04/2013 (dd/mm/yy).
The address of ActiveCell is AN2. This is the last cell from all the GETPIVOTDATA calculations.
So the part of the code that is giving the error is copying the row of new data from the GETPIVOTDATA calculations (T2:AN2), which is selected with this code:
Range(Cells(newRow, firstCol), ActiveCell).Select

copying it, and then pasting special - values (this is so that the cells have values in them, not a GETPIVOTDATA formula):
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

upon debugging it is this paste special values code that the error is coming up on.
Thanks in advance for your help
 
Upvote 0
Hi

If newRow and firstCol point to T2 then contrary to your suggestion of there being a date in that cell there is nothing!

Otherwise this section of code :-
Code: 
        Range("T2").Select
        Do While ActiveCell.Value <> ""
            ActiveCell.Offset(1, 0).Select
        Loop
        newRow = ActiveCell.Row
        firstCol = ActiveCell.Column
would have moved the ActiveCell to row 3.

I don't see that you set newRow anywhere else in the code.

hth
 
Upvote 0
Hi Mike and All,
Sorry for the late reply. As I didn't write the original code I'm not sure what exactly was happening with newRow and firstCol, so I've changed that part of the code to make more sense to me.
This code should do the same thing but for some reason is still failing at the copy and paste special values section:
Code: 
Sub MatchGraphs()
 
Dim ws As Worksheet
'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("T" & Cells.Rows.Count).End(xlUp).Offset(1, 0).Select
       
        '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]), """")"
   
    Dim Range1 As Range
    Set Range1 = Range("T2:AN27")
   
    Range1.Select
    Selection.Copy
    Range("T2").PasteSpecial xlPasteValues
   
    End If
Next ws
   
End Sub
Is there something I'm missing when I want to copy a range of cells across multiple active worksheets and then paste that data as values across multiple active worksheets?
Essentially all I want to do is change the formulas to values on each sheet. This is driving me mad! Thanks again guys for any assistance
 
Last edited:
Upvote 0
Hi

This section selects the range you want to copy :-
Code: 
    Dim Range1 As Range
    Set Range1 = Range("T2:AN27")
   
    Range1.Select
    Selection.Copy
    Range("T2").PasteSpecial xlPasteValues

and pastes it as values.

How sure are you that the Range to be copied ends at row 27 having placed the "new data" one row after the last row?

Change :-
Code: 
this :-
   Range1.Select
    Selection.Copy
    Range("T2").PasteSpecial xlPasteValues

to this :-
   Range1.Copy Destination:=Range1.PasteSpecial xlPasteValues
The former may have a conflict in that the source and destination appear to be different sizes.

hth
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Run-time error ‘438’ — “Object doesn’t support this property or method.”
Replies
3
Views
313
MARK858
MARK858
G
  • Question
How can i extract the correct leave time on this code?
Replies
1
Views
373
rlv01
R
B
  • Question
VBA to open outlook template a x number of times- getting run-time error '438' object doesn't support this property or method
Replies
1
Views
266
Norie
Norie
M
  • Solved
Could you help me please with "run time error 1004 size property of the font class"
Replies
4
Views
365
DanteAmor
DanteAmor
K
  • Question
Modifying Existing VBA Code to add 2 additional data columns
Replies
0
Views
153
keranali
K

Forum statistics

Threads
1,215,461
Messages
6,124,952
Members
449,198
Latest member
MhammadishaqKhan

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top