A macro to Copy/Paste a varying number of rows?? I have data in on sheet \"Order

Question

A macro to Copy/Paste a varying number of rows??

I have data in on sheet "Orders" in rows A to N, each row is info on a quote, that may turn into an order. Row H contains a formula that tests other cells in the row to determine if the status of the data is a "quote", order or other. There is also conditional formatting for column H to change cell fill based on the cell display green for"quote", yellow for "order", red for "lost" etc

Hopefully, data entered today as a quote will eventually progress to another status.

Mgmt wants to copy/paste only active quotes as of the date of viewing the file, onto a summary sheet [in the same file]

I was hoping to assign a macro to a button, that would run when the manager wanted to update the"Pipeline " list

I have recorded keystrokes to accomplish this [below]

The macro below does:

1. delete existing data on sheet "pipeline" [I selected a large number of rows to delete, to be sure no old data was left]

2 Select sheet "orders", filter column H for the quote color

3 using keyboard strokes to select displayed rows [I could just select a huge number of rows to copy to be sure I get all of them,but hoping for a more elegant solution]

4. copy/paste to the "Pipeline " sheet [will always be A12 as upper left cell on paste range]

5. Remove filter on "orders" sheet

6 return to "pipeline" sheet

I thought that using keyboard stroke [CTRL][Home]; [shift][end][down arrow] etc to highlight the filtered rows/columns, rather than selecting cells by mouse click, the macro would auto-select the entire filtered rows, but it seems locked into whatever area was originally selected. If new quotes are added [always below the existing data,no blank rows] the new quotes are not picked up.

-------------------------------------------

Sub Copy_Pipeline()
'
' Copy_Pipeline Macro
' Copy open quotes to pipeline sheet
'
' Keyboard Shortcut: Ctrl+r
'
    Sheets("Pipeline").Select
    Range("A12:N202").Select
    Selection.Delete Shift:=xlUp
    Range("A12").Select
    Sheets("Orders").Select
    ActiveSheet.Range("$A$1:$N$235").AutoFilter Field:=8, Criteria1:=RGB(146, _
        208, 80), Operator:=xlFilterCellColor
    Range("A17").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("A17:N54").Select
    Selection.Copy
    Sheets("Pipeline").Select
    ActiveSheet.Paste
    Range("A12").Select
    Sheets("Orders").Select
    Application.CutCopyMode = False
    ActiveSheet.ShowAllData
    Range("E18").Select
    Selection.End(xlDown).Select
    Sheets("Pipeline").Select
    Range("C8").Select
End Sub

Thanks for help !!

Explanation / Answer

Hi..

Hi,

I think this emulates your code and would work up to the last row,

Sub Copy_Pipeline()
Dim LastRow As Long
LastRow = Sheets("Pipeline").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("Pipeline").Range("A12:N" & LastRow).Delete Shift:=xlUp

LastRow = Sheets("Orders").Cells(Rows.Count, "A").End(xlUp).Row
     Sheets("Orders").Range("$A$1:$N$" & LastRow).AutoFilter Field:=8, Criteria1:=RGB(146, _
         208, 80), Operator:=xlFilterCellColor
     
  
    Sheets("Orders").Range("$A$17:$N$" & LastRow).SpecialCells(xlCellTypeVisible).Copy __
    Sheets("Pipeline").Range("A1")
    
     Sheets("orders").Range("$A$1:$H$" & LastRow).AutoFilter Field:=8.

End Sub.

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.