PasteSpecial method of Range class failed

[Dweller95]

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:

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:

    While (sht < maxSht)
  
        Sheets(sht).Activate
        Call copyRange(findRange(2))
      
        ' Increment and grab next sheet
        sht = sht + 1
  Wend

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

 

[Fluff]

You are missing the s at the end of xlPasteValues

 

[Dweller95]

You are missing the s at the end of xlPasteValues

You're right, that's all it was. It's been driving me crazy not being able to figure out why... Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.