Pass Named Range to VBA Function to create CSV

[Jack_881]

Hi All

I am using the follwoing code:

Sub exportRangeToFishBowlPO()

Dim myCSVFileName As String
Dim myWB As Workbook
Dim rngToSave As Range
Dim fNum As Integer
Dim csvVal As String

Set myWB = ThisWorkbook
myCSVFileName = "C:\PurchaseOrder.csv"
csvVal = ""
fNum = FreeFile
Set rngToSave = Range("A1:C50")


Open myCSVFileName For Output As #fNum

For i = 1 To rngToSave.Rows.Count
For j = 1 To rngToSave.Columns.Count
csvVal = csvVal & Chr(34) & rngToSave(i, j).Value & Chr(34) & ","
Next
Print #fNum, Left(csvVal, Len(csvVal) - 2)
csvVal = ""
Next

Close #fileNumber
End Sub

For the highlighted code Set rngToSave = Range("A1:C50") I would like to pass a range reference to the VBA function as the range can change depending on the length of the purchase order. So I created an named range called "PORange". My question is how do I replace = Range("A1:C50") with the cell references contained in the Named Range "PORange"?

Many Thanks

Jack

 

[Anthony47]

If the name refers directly to the "range to save" (A1:C50, in the sample code) then you may just use Set rngToSave = Range("PORange")
If in PORange you write the address of the "range to save" then you will use Set rngToSave = Range(Range("PORange").Value)

Bye

 

[Jack_881]

Thankyou, problem solved!

 

[Anthony47]

You are welcome