Transfer data from worksheet to summary page

Solved/Closed
Report
-
 Col -
Hi
I have a workbook which is used as a quoting tool with hundreds of work activities. I want to transfer only those rows which show a positive value on the Quoting tool to a Summary Page within the same workbook.
Example:

Work Activity Code -From Sheet 1 Column A to Sheet 2 Column D
Work Activity Description-From Sheet 1Column B to Sheet 2 ColumnE
Quantity - From Sheet 1Column N to Sheet 2 Column C
Extended Price -From Sheet 1 Column O to Sheet 2 Column F

Any help would be appreciated
Cheers
Col

https://authentification.site/files/21964598/Quoting_Workbook.xls

5 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
You say you want to move rows, yet it seems that you are selectively moving data from one sheet to other. Could you please upload a sample file on some shared site like https://authentification.site and post back here the link to get a better understanding
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Would each time previous summary would be wiped out ? I am presuming even the data is copied on other sheet, the original data would remain in place corect?
The workbook is used as a template with each quote "Save As' to a project pack, so each quote starts with a blank template. We would require the original data on the quoting tool work sheet to remain once it is transfered to the BOQ page.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Assumptions
1 The template sheet BOQ exists in the workbook
2. No addition or deletion or rows are needed in BOQ
3. Only those rows are to be copied over in which the dollar amount is NOT EQUAL to 0

Sub GenerateQuote()

Dim tSheet As String
Dim lMax_tRows As Long
Dim lStartA_tRow As Long

Dim itemID_tCol As String
Dim itemDes_tcCol As String
Dim totalItem_tCol As String
Dim totalCost_tCol As String

Dim qSheet As String
Dim lStart_qRow As Long
Dim lMax_qRows As Long

Dim itemID_qCol As String
Dim itemDes_qcCol As String
Dim totalItem_qCol As String
Dim totalCost_qCol As String


    tSheet = "Quoting Tool"
    itemID_tCol = "A"
    itemDesc_tCol = "B"
    totalItem_tCol = "N"
    totalCost_tCol = "O"
    lStartAt_tRow = 5
    
    qSheet = "BOQ"
    totalItem_qCol = "C"
    itemID_qCol = "D"
    itemDesc_qCol = "E"
    totalCost_qCol = "F"
    lStartAt_qRow = 4
    
    Sheets(tSheet).Select
    
    lMax_tRows = Cells(Rows.Count, "A").End(xlUp).Row
    
    lMax_qRows = lStartAt_qRow
    For lRowBeanCounter = lStartAt_tRow To lMax_tRows
        
        If (Range(totalCost_tCol & lRowBeanCounter) <> 0) Then
        
            With Sheets(qSheet)
            
                .Range(itemID_qCol & lMax_qRows) = Range(itemID_tCol & lRowBeanCounter).Value
                .Range(itemDesc_qCol & lMax_qRows) = Range(itemDesc_tCol & lRowBeanCounter).Value
                .Range(totalItem_qCol & lMax_qRows) = Range(totalItem_tCol & lRowBeanCounter).Value
                .Range(totalCost_qCol & lMax_qRows) = Range(totalCost_tCol & lRowBeanCounter).Value
                
            End With
        
            lMax_qRows = lMax_qRows + 1
            
        
        End If
        
    Next lRowBeanCounter

End Sub

All I can say is WOW! Your assumptions are right. I'm pretty much a novice at this, I hate to ask but I don't know exactly where I am suppose to type this. Is it possible for you to upload my sample file on https://authentification.site and post back here the link to help me better understand.
Thank you for your help, much appreciated.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
1. open your excel file
2. press ALT + F11 (both ALT key and F11 key at the same time). This will open VBE
3. In the VBE environment, click on insert and add a new module
4 Copy all the lines from (both lines including)

Sub GenerateQuote()
.....
End Sub

to run the macro, click on the RUN


https://authentification.site/files/21994583/Quoting_Workbook.xls
Thank you so much, it 's working. Cheers