Macros

Hi Jay,

Thank you for your question and welcome to the forum.

This type of automation would have to be done in the Excel VBA window.

I have included a sample of code that creates a report based on the SalesPerson entered into an Input box. Everytime you run the macro you can choose a different salesperson.

The First IF statement could be changed to ask for the month instead.

What the following snapshot of code does is the following:

1. On your data sheet it says that If the first value in the month column is equal to the month you type into the input box then copy all the records in each row relating to that month into a new sheet.

2. Alternatively you would have to use a between...and filter to get the results for the month. You would have two input boxes, put the earliest date in followed by the latest and it will then run the report based on all the records between those two dates.

3. Once it has copied the first value in the row it has a loop to move to the next column in the same row. Once it finishes copying the first row, it then has a second loop that moves down to the row and then executes both loops until all the data has been copied.


--------------------------------------------------------------------------
SNAPSHOT OF SAMPLE CODE

If Sheets("Total Sales").Range("a10").Cells(intRowCount, 10).Value = strName Then _

For intColumnCount = 1 To 9 ' 1st 9 columns to be copied

Sheets(strName).Cells(intTargetRowCount, intColumnCount).Value = _
Sheets("Total Sales").Range("a10").Cells(intRowCount, intColumnCount).Value


Next intColumnCount

intTargetRowCount = intTargetRowCount + 1 ' moves you down to row 11 of strname sheet

End If

Next intRowCount

End Sub
---------------------------------------------------------------------------
This procedure is a small part of the overall result but hopefully gives you a little insight into how you would do it.

Regards

Simon