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:
The way I am calling this function is in my Sub Main() I use:
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