How to adjust macro?

Question

Below was a macro provided, but I need to know how to do some adjustments to save it to the file name fixed to a specific worksheet, and specific cell with dates( eg. worksheet name is: AA on cell "B2" with ZZ ). So the filename will be ZZ AA 23122008.csv

Thanks in advance.

---

Public Sub SaveWorksheetsAsCsv()

Dim WS As Excel.Worksheet
Dim SaveToDirectory As String

Dim CurrentWorkbook As String
Dim CurrentFormat As Long

CurrentWorkbook = ThisWorkbook.FullName
CurrentFormat = ThisWorkbook.FileFormat
' Store current details for the workbook

SaveToDirectory = "C:\temp\"

For Each WS In ThisWorkbook.Worksheets
  WS.SaveAs SaveToDirectory & WS.Name, xlCSV
Next

Application.DisplayAlerts = False
ThisWorkbook.SaveAs filename:=CurrentWorkbook, FileFormat:=CurrentFormat
Application.DisplayAlerts = True
' Temporarily turn alerts off to prevent the user being prompted
'  about overwriting the original file.

End Sub

---

Answer 1

U can initialize

ThisDate  = Format(Now(), "ddmmmyy")

and if you want to take the value of WS from second row, second column then

WS.SaveAs SaveToDirectory & WS.Name & WS.Cells(2,2) & ThisDate , xlCSV

will be your answer.. In more details...

Public Sub SaveWorksheetsAsCsv()

Dim WS As Excel.Worksheet
Dim SaveToDirectory As String

Dim CurrentWorkbook As String
Dim CurrentFormat As Long

CurrentWorkbook = ThisWorkbook.FullName

CurrentFormat = ThisWorkbook.FileFormat

' Store current details for the workbook '
ThisDate = Format(Now(), "ddmmmyy") 

SaveToDirectory = "C:\temp\"

For Each WS In ThisWorkbook.Worksheets
  WS.SaveAs SaveToDirectory & WS.Name & ThisDate & WS.Cells(2,2) , xlCSV
Next

......

and so on.

Answer 2

Thank you, lakshmanaraj for your quick response. But do you mind to elaborate where to initialize? or which line do I add to.

Answer 3

After adding the line for initialise the date and added the line WS.SaveAs SaveToDirectory & WS.Name & WS.Cells(2,2) & ThisDate , xlCSV I get an error 1004 application defined or object defined error. If I am not mistaken from the code, will it take each worksheet cell (2,2) and save it as per the work sheet. Can I use a fixed worksheet?

Answer 4

I have amended the codes and now it does save the different worksheet to a *.csv file. But now it keeps saving my current worksheets in the workbook to the *.csv name. How do I stop this?

Dim WS As Excel.Worksheet Dim SaveToDirectory As String

Dim CurrentWorkbook As String Dim CurrentFormat As Long Dim myName As String myName = myName & Application.Cells(2, 2) 'cell B2

MyMonth = Left(MonthName(Month(Date)), 3) MyDay = Day(Date)

CurrentWorkbook = ThisWorkbook.FullName

CurrentFormat = ThisWorkbook.FileFormat

' Store current details for the workbook '

SaveToDirectory = "C:\temp\"

For Each WS In ThisWorkbook.Worksheets WS.SaveAs SaveToDirectory & myName & Left(MonthName(Month(Date)), 3) & Day(Date) & WS.Name, xlCSV

Next

Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat

Application.DisplayAlerts = False

' Temporarily turn alerts off to prevent the user being prompted ' about overwriting the original file.