code_junkie

How to adjust macro?

Nov 13th, 2011
104
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.19 KB | None | 0 0
  1. Public Sub SaveWorksheetsAsCsv()
  2.  
  3. Dim WS As Excel.Worksheet
  4. Dim SaveToDirectory As String
  5.  
  6. Dim CurrentWorkbook As String
  7. Dim CurrentFormat As Long
  8.  
  9. CurrentWorkbook = ThisWorkbook.FullName
  10. CurrentFormat = ThisWorkbook.FileFormat
  11. ' Store current details for the workbook
  12.  
  13. SaveToDirectory = "C:temp"
  14.  
  15. For Each WS In ThisWorkbook.Worksheets
  16. WS.SaveAs SaveToDirectory & WS.Name, xlCSV
  17. Next
  18.  
  19. Application.DisplayAlerts = False
  20. ThisWorkbook.SaveAs filename:=CurrentWorkbook, FileFormat:=CurrentFormat
  21. Application.DisplayAlerts = True
  22. ' Temporarily turn alerts off to prevent the user being prompted
  23. ' about overwriting the original file.
  24.  
  25. End Sub
  26.  
  27. ThisDate = Format(Now(), "ddmmmyy")
  28.  
  29. WS.SaveAs SaveToDirectory & WS.Name & WS.Cells(2,2) & ThisDate , xlCSV
  30.  
  31. Public Sub SaveWorksheetsAsCsv()
  32.  
  33. Dim WS As Excel.Worksheet
  34. Dim SaveToDirectory As String
  35.  
  36. Dim CurrentWorkbook As String
  37. Dim CurrentFormat As Long
  38.  
  39. CurrentWorkbook = ThisWorkbook.FullName
  40.  
  41. CurrentFormat = ThisWorkbook.FileFormat
  42.  
  43. ' Store current details for the workbook '
  44. ThisDate = Format(Now(), "ddmmmyy")
  45.  
  46. SaveToDirectory = "C:temp"
  47.  
  48. For Each WS In ThisWorkbook.Worksheets
  49. WS.SaveAs SaveToDirectory & WS.Name & ThisDate & WS.Cells(2,2) , xlCSV
  50. Next
Add Comment
Please, Sign In to add comment