Transfer data from multiple sheets to sheet 1 based on criteria

[Closed]
Report
Posts
1
Registration date
Saturday August 22, 2015
Status
Member
Last seen
August 22, 2015
-
Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
-
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

Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
239
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.