Creating A CSV Output With An Excel Macro

In the past I’ve looked at introductions to excel macros. If you’re a beginner I recommend that you take a look at:

–Excel Macros – What Are Excel macros
–-Excel Macros – How To Build Excel Macros
–Excel Macros – How To Build Excel Macros Part II

-Our Excel Beginner’s book

One of the more common things that I do with my work macros is the creation of files. So I’d have one big file where all of my data resides. Then, every day, I’ll need to either save to a separate file or send by email some of that data, sometimes in an excel file but even more commonly in a .csv format. There is very little difference between the 2 in any case.

Today, I thought I’d do a very small into to how that can be done. Automating this process reduces errors but also makes it much easier to do. This can be used to send data to a co-worker, a client, supplier, etc.

Suppose that you have a big spreadsheet with dozens of sheets and you need to save one specific range to a csv. How would you do it? Simple. You’d create a macro that looks a little like this:

******************************************
Sub Macro1()
Sheets(“YUA”).Select
Range(“A4:H7”).Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ChDir “C:\Users\cantinpe\Desktop”
ActiveWorkbook.SaveAs Filename:=”C:\Book2.csv”, _
FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Save
ActiveWindow.Close
End Sub
******************************************

As you can imagine, this would take the sheet “YUA”, copy the specific range and save it to my C drive.

Other steps I could then do would be saving the file using today’s date and adding a button to make it even easier. I’ll try to do that in my next post:)

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss

***************************************************