Hi
I am actually trying to copy named ranges (have 10 different named range sections) to a new csv file for each of the named range.
Instead of copying the formula forma, I want to copy just the values of the named range.
So here is my macro in MODULE :-
VB:
Sub sheetToCSV()
Dim MyPath As String
Dim MyFileName As String
'The path and file names:
MyPath = "D:\_ShopFloor BI queries"
MyFileName = "MyFileName" & 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"
'This copies the sheet to a new workbook. Change the name between "" to your sheet name.
'Sheets("Book2").Copy
ThisWorkbook.Names("Day_AC_Out").RefersToRange.Select
Selection.Copy
Workbooks.Add
ActiveSheet.PasteSpecial xlPasteValues
Application.CutCopyMode = False
'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
ActiveWorkbook.Save
ActiveWindow.Close
Range("Day_AC_Out").Select
Selection.ClearContents
End Sub
And MACRO IN THISWORKBOOK:-
VB:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call sheetToCSV
End Sub
I am getting ERROR in the module section " ActiveSheet.PasteSpecial xlPasteValues"
Any suggestions...
ps:- I am pretty new to VB macro stuff so plz be patient.
Bookmarks