Pass Named Range to VBA Function to create CSV

Jack_881

New Member
Joined
Sep 11, 2020
Messages
11
Office Version
  1. 2019
Platform
  1. Windows
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,223
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
 

Forum statistics

Threads
1,140,917
Messages
5,703,168
Members
421,279
Latest member
emzy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top