Previous Topic Next Topic

Transfer data from multiple sheets to sheet 1 based on criteria

Closed
catyana Posts 1 Registration date Saturday August 22, 2015 Status Member Last seen August 22, 2015 - Aug 22, 2015 at 03:03 PM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Aug 23, 2015 at 06:06 AM
Hello,

I am creating an estimating workbook where I have multiple product sheets that list qty, part#, description, volume, lbs, list price & discount for each row. I would like to be able to enter a qty greater than 0 into the rows I want to include in the estimate and have just those rows populate in the "Estimate sheet". Basically I'm looking to automation the copy and past function based on there being a quantity entered in column A on the various product sheets.

Is this doable?

Thanks :)

Related:

1 response

Answer 1 / 1
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Aug 23, 2015 at 06:06 AM
Hello Catyana,

See if the following code helps:- 

Sub CopyData()

Application.ScreenUpdating = False

    Dim ws As Worksheet
    Dim lRow As Long
    
Sheets("Estimate").Range("A3:G" & Rows.Count).ClearContents

For Each ws In Worksheets
    If ws.Name = "Estimate" Then GoTo NextSheet
    
    ws.Select
    
lRow = Range("A" & Rows.Count).End(xlUp).Row
    
    For Each cell In Range("A2:A" & lRow)
        If cell.Value > 0 Then
            Range(Cells(cell.Row, "A"), Cells(cell.Row, "G")).Copy
            Sheets("Estimate").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial
        End If
    Next cell

NextSheet:
Next ws

Sheets("Estimate").Select
Application.ScreenUpdating = True
Application.CutCopyMode = False

End Sub


You can peruse my test work book at the following link:-

https://www.dropbox.com/s/ddtbnl5b36mnevp/Catyana.xlsm?dl=0

The code searches Column A in each sheet for any value greater than zero and then transfers the data of each row with a value in Column A to the "Estimate" sheet. When you are finished with the transferred data in the "Estimate" sheet, the "used" data in the "Estimate" sheet is cleared ready for your next lot of data when you next execute the code.

I hope that this helps.

Cheerio,
vcoolio.
0