Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 22
  • Last Modified:

How to save two excel tabs as .csv with a macro?

I have an excel spreadsheet with 5 tabs. I am looking for a macro that I can run and it will save two of the tabs to a specific location on my network. The two tabs I want to save as a CSV are "Sales1" and "Sales2". I am wanting to save those tabs to "R:\Sales\" folder. I would like them to be a separate csv. So one would be called "Sales1.csv" and the other "Sales2.csv". Thanks!
0
brasiman
Asked:
brasiman
  • 3
2 Solutions
 
xtermieCommented:
Something like:
  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 = "R:\Sales\"
   For Each WS In ThisWorkbook.Worksheets
   If WS.Name = "Sales1" Or WS.Name = "Sales2" Then
         WS.SaveAs SaveToDirectory & WS.Name, xlCSV
   End If
   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
 

Open in new window

0
 
brasimanAuthor Commented:
Thank you xtermie! That is perfect!!!! A side question. In the future I might want to change the file name to something different when I save the Sales1 and Sales2 tabs. I'm thinking in the future, I will have Sales1 save as "Sales1Export" and the Sales2 tab as "Sales2Export". Just so I can differentiate the reports vs exports. Is that possible?
0
 
xtermieCommented:
Yes u can
In the above code code just set the new name as follows

For Each WS In ThisWorkbook.Worksheets
      If WS.Name = "Sales1" Or WS.Name = "Sales2" Then
      Myfilenane = WS.Name & "_export"      
WS.SaveAs SaveToDirectory &  Myfilename, xlCSV
   End If
   Next
0
 
xtermieCommented:
Solution provided and author verified the solution was good
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now