Running a report using a macro
 

Hi there

I'm hoping someone can help me with this... I'm wanting to run a report from
a worksheet that has around 15000 lines of data, based on two criteria. I
have tried using Autofilter, but my computer keeps crashing.

The report I'd like to run is based on a particular month. So say in the
reporting worksheet (called Report) if I type in the month I'm interested in,
in say cell A1, and have a button to press with the macro behind it, then the
Macro would look at the data worksheet (called Data) and find all the records
relevant to that particular month. (The month data in the data worksheet is
in column K.) Also at the same time I would like the macro to look at Column
E and find all the lines that say "TEST".

At the same time, if there is previous data on the €œReport€� worksheet, can
this be deleted before the new information is added.

So the report on the "reporting" worksheet would copy all the lines from the
"Data" worksheet that are from the month in cell A1, and have "TEST" in
column E. The data on the data worksheet ranges from A to N.

I hope this makes sense! And any help would be greatly appreciated.

Rachael

Running a report using a macro
 

This assumes headers in row 1. If there are no headers then delete the
line:
sh1.Range("A1:N1").Copy sh2.Range("A1")

Sub getData()
Dim lr As Long, rng As Range
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("Data")
Set sh2 = Sheets("Report")
lr = sh1.Cells(Rows.Count, 11).End(xlUp).Row
Set rng = sh1.Range("K2:K" & lr)
sh2.Cells.Clear
sh1.Range("A1:N1").Copy sh2.Range("A1")
For Each c In rng
lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row
If c.Value = sh1.Range("A1").Value And _
UCase(Range("E" & c.Row)) = "TEST" Then
Range("A" & c.Row & ":N" & c.Row).Copy _
sh2.Range("A" & lr2 + 1)
End If
Next
End Sub



"rmsmith" wrote in message
...
Hi there

I'm hoping someone can help me with this... I'm wanting to run a report
from
a worksheet that has around 15000 lines of data, based on two criteria. I
have tried using Autofilter, but my computer keeps crashing.

The report I'd like to run is based on a particular month. So say in the
reporting worksheet (called Report) if I type in the month I'm interested
in,
in say cell A1, and have a button to press with the macro behind it, then
the
Macro would look at the data worksheet (called Data) and find all the
records
relevant to that particular month. (The month data in the data worksheet
is
in column K.) Also at the same time I would like the macro to look at
Column
E and find all the lines that say "TEST".

At the same time, if there is previous data on the "Report" worksheet, can
this be deleted before the new information is added.

So the report on the "reporting" worksheet would copy all the lines from
the
"Data" worksheet that are from the month in cell A1, and have "TEST" in
column E. The data on the data worksheet ranges from A to N.

I hope this makes sense! And any help would be greatly appreciated.

Rachael