Excel VBA: Passing a range name as a function argument

Question

I'm writing a macro to do a complex copy/paste exercise. It's pretty straightforward conceptually, but I'm stuck in one spot. All of the various blocks of data are identified with various named ranges. I need iterate through this list of names, passing each name as an argument to a function (actually a subroutine, but same idea). The source of the data is in one workbook while the destination is in another workbook.

Here is what I have (for just one block of data):

Private Sub copyABU()
   copyPaste(ThisWorkbook.Names("myRange1").RefersToRange)
   copyPaste(ThisWorkbook.Names("myRange2").RefersToRange)
   copyPaste(ThisWorkbook.Names("myRange3").RefersToRange)
   //etc
End Sub

Private Sub copyPaste(thisRange As Range)
    Windows(someworkbook).Range(thisRange).Copy
    Range(thisRange).PasteSpecial Paste:=xlPasteValues
End Sub

Unfortunately, I get a run-time error on this. I think that there is a type mismatch, but I'm not sure about this and can't figure out what I am missing. Can anyone see why this fails? (I'm using Excel 2010).

Thanks!

Answer 1

Your code will work with a couple small tweaks.

First, you need to prefix your call to copyPaste with the word Call. (See note below if you don't want to.)

Private Sub copyABU()
   Call copyPaste(ThisWorkbook.Names("myRange1").RefersToRange)
   Call copyPaste(ThisWorkbook.Names("myRange2").RefersToRange)
   Call copyPaste(ThisWorkbook.Names("myRange3").RefersToRange)
   ' //etc
End Sub

Second, add a .Address after thisRange.

Private Sub copyPaste(thisRange As Range)
    Range(thisRange.Address).Copy
    thisRange.PasteSpecial Paste:=xlPasteValues
End Sub

I didn't want to bother with creating a someworkbook variable, so I just deleted that part.

Note: You have to use the Call keyword if you are calling a procedure with an argument list enclosed in parentheses. https://stackoverflow.com/a/7715070/138938

If you don't want to use the Call keyword, omit the parens:

copyPaste ThisWorkbook.Names("myRange1").RefersToRange