Trying to paste range object that gets passed as parameter

Dtex20

Board Regular
Joined
Jan 29, 2018
Messages
50
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).


Code:
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.

Code:
                 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
 
Last edited:

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
If pasteRan is a range object then you don't need Range and you don't need a worksheet reference.
Code:
pasteRan.PasteSpecial xlPasteValues
pasteRan.PasteSpecial xlPasteFormats
pasteRan.FormatConditions.Delete
 
Upvote 0
If pasteRan is a range object then you don't need Range and you don't need a worksheet reference.
Code:
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 :ROFLMAO:
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

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