PasteSpecial method of Range class failed

Dweller95

New Member
Joined
Nov 11, 2021
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
Hi everyone. I'm still new to VBA and running into an issue when trying to create two functions that take a parameter to find the defined range and then copy paste that range into a new sheet.

Why am I being thrown this "PasteSpecial method of Range class failed" error in my copyRange function? I also don't want to specify specific sheet names if that's part of the issue because I loop through all of the sheets grabbing data for those specific ranges on each sheet.

The error points to my copyRange function on this line specifically:
VBA Code:
Cells(Range("A" & rows.Count).End(xlUp).row + 1, 1).PasteSpecial xlPasteValue

The way I am calling this function is in my Sub Main() I use:
VBA Code:
    While (sht < maxSht)
  
        Sheets(sht).Activate
        Call copyRange(findRange(2))
      
        ' Increment and grab next sheet
        sht = sht + 1
  Wend


VBA Code:
Function findRange(ByVal chooseSet As Integer) As Range
  
    ' Initialize variables
    Dim startRow As Integer
    Dim lastRow As Integer
  
    ' If findRange is passed '1' then set range equal to tableA
    ' else if passed '2' then set range equal to tableB
    ' else if passed '3' then set range equal to tableC
    ' Do nothing if not one of those three values
    If (chooseSet = 1) Then
        lastRow = Cells.Find("Total", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
        Set findRange = Range("A3:F" & lastRow)
        Exit Function
    ElseIf (chooseSet = 2) Then
        lastRow = Cells.Find(What:="Total", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
        Set findRange = Range("L3:Q" & lastRow)
        Exit Function
    ElseIf (chooseSet = 3) Then
        startRow = Cells.Find(What:="Total", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
        startRow = startRow + 3
        lastRow = Range("L" & rows.Count).End(xlUp).row
        Set findRange = Range("L" & startRow & ":Q" & lastRow)
        Exit Function
    End If
 
End Function

Function copyRange(ByVal cpRange As Range)
  
    ' Grab range from findRange function
    cpRange.Copy
  
    ' Copy range to DATA sheet and paste into first available cell
    Sheets("DATA").Activate
    Cells(Range("A" & rows.Count).End(xlUp).row + 1, 1).PasteSpecial xlPasteValue
    Application.CutCopyMode = False

End Function
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
You are missing the s at the end of xlPasteValues
 
Upvote 0
Solution
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,948
Messages
6,175,571
Members
452,652
Latest member
eduedu

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