How to use variables in range assignments

Question

I would like to use variables that I can pass to a SUB for coping an array from one sheet to another sheet in another workbook. Without variables this works well for me:

Dim RNG1 as Range, RNG2 as Range
Sub CopyRange()
    Set RNG1 = Workbooks("Program.xlsm").Sheets("Input").Range("G9:G19")
    Set RNG2 = Workbooks("Data.xlsx").Sheets("Reformatted").Range("A3:A13")
    RNG1.Copy RNG2
End Sub

I would like to be able to pass "Data.xlsx" as a variable to the SUB. I've tried using String variables, but have not found the solution.

Any help would be appreciated.

Tim Williams · Accepted Answer · 2013-10-24 21:56:56Z

1

Sub CopyRange(wbName as string)
    Dim RNG1 as Range, RNG2 as Range 'these need only to be Local variables
    Set RNG1 = Workbooks("Program.xlsm").Sheets("Input").Range("G9:G19")
    Set RNG2 = Workbooks(wbName).Sheets("Reformatted").Range("A3:A13")
    RNG1.Copy RNG2
End Sub

Use:

CopyRange "Data.xlsx"

answered Oct 24, 2013 at 21:56

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joseph · Accepted Answer · 2013-10-24 21:32:33Z

0

How about this?

Dim RNG1 As Range, RNG2 As Range
Sub test()
    Call CopyRange(Workbooks("Data.xlsx"))
End Sub

Sub CopyRange(ByRef wb As Excel.Workbook)
    Set RNG1 = Workbooks("Program.xlsm").Sheets("Input").Range("G9:G19")
    Set RNG2 = wb.Sheets("Reformatted").Range("A3:A13")
    RNG1.Copy RNG2
End Sub

answered Oct 24, 2013 at 21:32

Joseph

5,2001 gold badge27 silver badges26 bronze badges

Collectives™ on Stack Overflow

How to use variables in range assignments

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related