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 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 - 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 :)

1 reply

vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252
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