Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Formula
I want to be able to have a row of data automatically imported to another
sheet within the workbook based on the criteria of one cell. Example: I have a complete project list and each project has a status I want a copy of the rows data to be input into another sheet based on the €śStatus€ť column. Such as estimating or in progress. Is this possible?? I dont want to have to manually retype a new sheet based on the status of the project. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Formula
You can filter (Data-Filter-AutoFilter), click the dropdown next to
Status, and select the status you want. Then, copy the rows to a new worksheet. This is not quite "automatic". You can use some code to do this quickly. Assuming the following: * You have your data on Sheet1, in a range A1:K687 * You have four possible status codes: "Completed", "In Progress", "Estimating", "Not Started" * You have four additional worksheets, named by status codes "Completed", "In Progress", "Estimating", "Not Started" * You have the status code on your data sheet contained in column 3 * There are no blanks in your data that would cause Excel to not recognize it as a "region" Use this code in a standard module of your workbook. Public Sub StatusReport() Dim StatusOptions As Variant Dim rngData As Excel.Range Dim i As Long Const intStatusColumn as Long = 3 Const strDataRange As String = "$A$1:$K$687" Set rngData = Sheet1.Range(strDataRange) StatusOptions = Array("Completed", "In Progress", "Estimating", "Not Started") For i = LBound(StatusOptions) To UBound(StatusOptions) rngData.AutoFilter intStatusColumn, StatusOptions(i) rngData.CurrentRegion.Copy _ ThisWorkbook.Worksheets(StatusOptions(i)).Range("A 1") Next i ' clear the filter rngData.AutoFilter intStatusColumn End Sub On Oct 22, 1:06 pm, jen wrote: I want to be able to have a row of data automatically imported to another sheet within the workbook based on the criteria of one cell. Example: I have a complete project list and each project has a status I want a copy of the rows data to be input into another sheet based on the "Status" column. Such as estimating or in progress. Is this possible?? I don't want to have to manually retype a new sheet based on the status of the project. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|