Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Report with macro losing links to a particular worksheet after running macro | Excel Programming | |||
When running a SAP report Excel will only display results if Excel isn't already open | Excel Discussion (Misc queries) | |||
How do I create a running report on Excel | New Users to Excel | |||
How do I produce a running Census report in Excel? | Excel Worksheet Functions | |||
Old report suddenly not running | Excel Programming |