Hi,
I have written this macro to convert into a csv file to run for all defined named ranges in the activesheet. It run jst perfect when I hit SAVE button and it creates that many different CSV files for each named range.
However I am trying to use same macro in the another file and the problem I am facing is there a lot more named ranges and I want to run the macro for only selected NAMED RANGE. In this case 2 Named Range / 24 Named range.
Can you advise on what part of code do I need to change and to what to make it work for jst 2 named ranges ?
Sub sheetToCSV()
Dim MyPath As String
Dim MyFileName As String
Set rnl = ActiveWorkbook.Names 'rnl = RangeNameList variable that holds all the names available in the workbook.
Dim counter As Integer
Dim ListNumber As Integer
ListNumber = rnl.Count
'The path and file names:
MyPath = "D:\_ShopFloor BI queries"
'Loop for file creation.
For counter = 1 To ListNumber
MyFileName = rnl(counter).Name & "-" & Format(Date, "ddmmyy")
'Validation: Making sure the path name ends with "\":
If Not Right(MyPath, 1) = "\" Then MyPath = MyPath & "\"
'validation: Making sure the filename ends with ".csv"
If Not Right(MyFileName, 4) = ".csv" Then MyFileName = MyFileName & ".csv"
ThisWorkbook.Names(rnl(counter).Name).RefersToRange.Select
Selection.Copy
Workbooks.Add
ActiveCell.PasteSpecial xlPasteValues
'The new workbook becomes Activeworkbook:
With ActiveWorkbook
'Saves the new workbook to given folder / filename:
.SaveAs Filename:= _
MyPath & MyFileName, _
FileFormat:=xlCSV, _
CreateBackup:=False
'Closes the file
.Close False
End With
Next
Set nms = ActiveWorkbook.Names
Debug.Print nms.Count
Debug.Print (nms(1).Name)
End Sub
I want to run for NAMED RANGE - CMMDTY and BUSGROUP only.
please advise
Bookmarks