CODEWITHSUNDEEP
vbavariablesexcelrange
user2913428
user2913428

Reputation: 11

How to use variables in range assignments

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.

Upvotes: 0

Views: 89

Answers (2)

Tim Williams
Tim Williams

Reputation: 166146

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"

Upvotes: 1

Joseph
Joseph

Reputation: 5160

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

Upvotes: 0

Related Questions