Transfer data from worksheet to summary page

Solved/Closed
Col - Apr 15, 2010 at 02:18 AM
 Col - Apr 18, 2010 at 04:41 PM
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

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Apr 15, 2010 at 11:39 AM
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
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Apr 15, 2010 at 06:11 PM
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?
0
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.
0

Didn't find the answer you are looking for?

Ask a question
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Apr 16, 2010 at 12:32 PM
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

0
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.
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Apr 17, 2010 at 08:53 PM
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
0
Thank you so much, it 's working. Cheers
0