Trying to paste range object that gets passed as parameter

[Dtex20]

Hi guys,

I didn't know quite what to title the thread. I have this function i pass ranges through, which copies the range and should paste it on a certain range on the spreadsheet. I'm having trouble with the .paste, section of the function. I define the ranges before passing through the parameters(ranges).

Sub cutHistory(control As IRibbonControl)

Dim copyRan As Range
Dim pasteRan As Range
Dim sRowN As Double
Dim cel As Range
Dim ws As Worksheet


Set ws = ThisWorkbook.Sheets("Holiday History")




For Each cel In Worksheets("Holiday History").Range("B5:B3500")
                  
                
    If IsEmpty(cel.Value) Then
       sRowN = cel.Row
    Exit For
    End If
                          
    Next cel
        
                          
    If sRowN = 0 Then
    MsgBox "Please Increase The Range In Module5 On The For Each Loop!"
    End If
    


If Not Application.Intersect(ActiveCell, Range("B15:B3500")) Is Nothing Then




 Set copyRan = Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, 12))
 Set pasteRan = ws.Range(ws.Cells(sRowN, 2), ws.Cells(sRowN, 14))
     Call historyFUNC(copyRan, pasteRan, sRowN)

This is the first part of my code. Which seems to work fine.

This below, is the function it has the parameters defined. It's just the paste that is not working.

                 Set ws = ThisWorkbook.Sheets("MainSheet")                 Set mySel = copyRan
                
                                    For Each aCell In mySel
                                    With aCell
                                    .Font.FontStyle = .DisplayFormat.Font.FontStyle
                                    .Interior.Color = .DisplayFormat.Interior.Color
                                    .Font.Strikethrough = .DisplayFormat.Font.Strikethrough
                                    End With
                                    Next aCell
                                    
                 mySel.Copy


                  


                   With Worksheets("Holiday History")
                   .Range(pasteRan).PasteSpecial xlPasteValues
                   .Range(pasteRan).PasteSpecial xlPasteFormats
                   .Range(pasteRan).FormatConditions.Delete
                   End With

This is the part i'm having a problem with, .Range(pasteRan). I can't figure out how to paste to the range i'm getting:

Application defined or Object defined error. Maybe the paste range can't be found?

Thanks for the help guys, this forum has been a holy grail since i started VBA and Excel

 

[Norie]

If pasteRan is a range object then you don't need Range and you don't need a worksheet reference.

pasteRan.PasteSpecial xlPasteValues
pasteRan.PasteSpecial xlPasteFormats
pasteRan.FormatConditions.Delete

 

[Dtex20]

If pasteRan is a range object then you don't need Range and you don't need a worksheet reference.

pasteRan.PasteSpecial xlPasteValues
pasteRan.PasteSpecial xlPasteFormats
pasteRan.FormatConditions.Delete

HAHAHAA! i thought i had tried that, i swear i did. Thanks man. i did the hard work and forgot that