Excel Macro report creation

Question

I am trying to create an Excel macro that will take raw data (from an Excel Worksheet) and make worksheets organized by month within that Excel file. For example, I have a date column with data in the following format MM/DD/YYYY HH:MM:SS and I want the macro to create new Excel worksheets for each month. So by the end of the process the months (say Jan-March) are each individual worksheets and the raw data is sheet is still intact (unchanged).

I am fairly new to writing Excel macros so any help would be appreciated.

Answer 1

The best thing you can do when getting started with excel macros is to record what you want to do and then inspect and refactor the code that excel generates for you. In your case, go Tools;Macro;Record new macro. Then create a sheet. Then choose the first cell containing a date and modify its value. Then select the row and copy it to the new sheet. Inspect the macro (Tools;Macro;Visual basic editor) and you should see the bones of inserting a new sheet, getting the value of a cell and copying a row of data.

Now throw in a CDate function to turn your date cell into a date, a DatePart to extract the month and a for loop to go through your source data and you'll be off to a good start!