CODEWITHSUNDEEP
excelvba
AndroidDev
AndroidDev

Reputation: 21237

Excel VBA: Passing a range name as a function argument

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!

Upvotes: 3

Views: 28010

Answers (2)

systrue
systrue

Reputation: 99

here my answer:

Sub init()
Windows("Book1.xlsx").Activate
Call copyPaste(2, Range(Cells(1, 1), Cells(10, 10)), "copy")
Windows("Book2.xlsx").Activate
Call copyPaste(1, Range(Cells(1, 1), Cells(10, 10)), "paste")
End Sub

Function copyPaste(wksInt As Integer, thisRange As Range, copyPasteStr As String)
    Dim workSheetRange As Range
    With Worksheets(wksInt)
        Set workSheetRange = thisRange
    End With
    workSheetRange.Select
    If copyPasteStr = "copy" Then
        Selection.Copy
    Else
        Worksheets(wksInt).Paste
    End If
End Function

This works as long as the Ranges for copy and paste are the same dimension. If the dimension varies, you need to change workSheetRange.Select into the condition and only select the first cell of the range for pasting. PAX

Upvotes: 1

Jon Crowell
Jon Crowell

Reputation: 22338

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

Upvotes: 1

Related Questions