Macro required to simplify monthly reporting

mark9988

Board Regular
Joined
Sep 30, 2005
Messages
90
Hello everyone,

I would greatly appreciate assistance in developing a macro to assist in my monthly reporting.

Within my yearly report, each month I create a report off of my previous month report. For example, my current report includes Jan-09 & Feb-09 data. Next week I will be adding Mar-09 data to this report in the empty rows below where Feb-09 ended. I guess you can consider this as a this a cumulative report as 12 months of data (Jan-09 - Dec-09) will eventually reside within this file.

Here is my problem, I need to distribute this report to several departments within the corporation I work for, and they are only interested in receiving the current month within the report. Thus, next week when I update this report they would only expect to receive Mar-09 data.

As a result, each month I manually delete rows containing prior month's data; next week I will manually delete rows containing Jan-09 & Feb-09 data. This may not seem like a problem, but the number of rows pertaining to each month can exceed 3,000 rows. Also, I have at least six tabs that require this exercise so as you can imagine, this task can become very tedious. Lastly, I'm usually forced to delete these rows in groups of 500 since my Excel freezes due to the formulas within each row.

Here is my goal; I would like to develop a Macro that would allow me to choose the months of data that require deletion. In this case, I would command the Macro to delete rows that have Jan-09 & Feb-09 in Column A (the reporting month will always be included in Column A as illustrated below).

Since I need this macro to run across several tabs, I would like to have the option to select the tabs that require this Macro to be executed within the file.

I'm hoping this request makes sense. Please allow me to clarify if I'm confusing you.

Best Regards,
Mark9988
example.xls
ABCDEFGHIJKLM
1Reporting MonthCustomer NameAccount Number #1Account Number #2Remaining TermCalendar Month End DateRental PaymentPayment Remit DatePayments Past DueStatus CodeLast Post DateNext Due DateCurrent Balance
2Jan-09Customer - xyx
3Jan-09Customer - abc
4Jan-09Customer - dfh
5Jan-09Customer - elk
6Jan-09Customer - qor
7Jan-09Customer - vnb
8Jan-09Customer - lkj
9Feb-09Customer - poi
10Feb-09Customer - zxc
11Feb-09Customer - aer
12Mar-09Customer - mne
13Mar-09Customer - uyt
14Mar-09Customer - qwp
15Mar-09Customer - asl
Rentals
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
try this
Code:
Sub PriorDelete()
Dim LR As Long, i As Long
Application.ScreenUpdating = False

LR = ActiveSheet.Cells(65536, 1).End(xlUp).Row

    For i = LR To 2 Step -1
        If Month(Cells(i, 1)) < Month(Now()) Then
                Rows(i).Delete
        End If

    Next i
MsgBox "Done"
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Hi texasalynn,

There are a number of ways you could do it.

A macro to hide the rows you don't want on your selected tabs

Create a Month Worbook and copy over just the Month records you want.

Or as you describe delete the Months you don't want

What's your preference.

Regards

ColinKJ
 
Upvote 0
Hi ColinKJ,

I'm not sure if your question was directed towards me or texasalynn. Regardless, hiding the rows wouldn't work since the file would eventually become to large to send via email (even if zipped).

Creating a seperate workbook and copying over sounds like a good idea, but it won't work since I need the cummulative report for my own reference. In my reporting role, I compare metrics/variances. Also, copying data over may become an issue as formulas reside within each row and manual errors are possible.

My only option is to have the functionality through a Macro to select months/rows to delete.

Texasalynn,

Thank you for the Macro, I will try it now.

Thanks,
Mark9988
 
Upvote 0
Sorry Mark9988, it was directed to you.

I saw texasalyn post as I was posting.

texasalyn's macro should do the job, but the creating a copy with just the month data would leave your original data intact, just creat a seperate month report.

ColinKJ
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top