Consolidating data entries across multiple worksheets to one sheet
 

I'm attempting to create an Excel file where a user could enter a quantity
needed of a particular part number in a column listing of various parts and
then to be able to have only those rows with a quantity entry be consolidated
to a separate sheet...
1 35200 Screen, S.S.
37733 Screen Assembly, Teflon(R)
15 37798W Weep hole union, S.S. 1/4"
37801W Weep hole union, S.S. 3/8"

Ideally, after entering quantities for separate items, I'd like to have only
those items with a quantity value entered be consolidated and reported on one
sheet. I have multiple worksheets with different part numbers and associated
item descriptions and would like to be able for the user to go through the
multiple pertinent worksheets, enter the needed quantities as needed from
each worksheet and then generate a single report detailing only those lines
that have a quantity entered from all worksheets. In the above, my desired
report would reflect--
1 35200 Screen, S.S.
15 37798W Weep hole union, S.S. 1/4"
Can this be done and, if so, how would one set it up?

Consolidating data entries across multiple worksheets to onesheet
 

Excel 2007
Pivot Table
Macro
No Formulas
Dynamic
http://www.mediafire.com/file/mymmgjqzgyy/01_28_09.xlsm

Consolidating data entries across multiple worksheets to onesheet
 

Excel 2007
Or merge Tables with Macro.
Tables can be any size or column order.
Filter resulting Table using various methods.
http://www.mediafire.com/file/ai3z5l...01_28_09a.xlsm

Consolidating data entries across multiple worksheets to one s
 

Herbert --
Many, many thanks for the guidance and examples. I've been able to modify it
to 'almost' suit my needs but I've added additional worksheets (#1-renamed to
"Quote Request Summary"; renamed your List1/List2/List3 sheets and added 10
additional worksheets.)

Currently, the following code only consolidates data from the former
List1-List2-List3 worksheets and not the additional added worksheets. How can
the following code be modified to:

1. -- Delete the Loc/Price/Cost columns;
2. -- Reorder the remaining columns to place Qty first then Part then Name
3. -- Evaluate and consolidate data with Qty entries 0 in all of the
worksheets?
4. -- Clear all Qty values in all worksheets upon file close

Thanks in advance...!

Current Code--

Option Explicit

Sub CombineTs()
Dim rr As Integer 'row count of Quote Request Summary
Dim n As Integer 'sheet count
Dim m As Integer 'column count Quote Request Summary
Dim t As Integer
Dim s() As Variant 'row count others
Dim u As Integer 'cumulative row count
Dim ss As String 'name of Quote Request Summary
Dim sc() As Variant 'column names in Quote Request Summary
Dim ssc() As Variant 'column count others
Dim i As Integer
Dim j As Integer

ss = "Quote Request Summary"

Application.ScreenUpdating = False
Application.DisplayAlerts = False

n = ActiveWorkbook.Worksheets.Count
ReDim s(n + 1)
ReDim ssc(n + 1)
u = 1
For t = 1 To n
If Not ss = Worksheets(t).Name And Worksheets(t).ListObjects.Count = 1 Then
s(t) = Worksheets(t).ListObjects(1).ListRows.Count
ssc(t) = Worksheets(t).ListObjects(1).ListColumns.Count
End If
Next t

With Sheets("Quote Request Summary").ListObjects(1) 'Delete old data
.Range.AutoFilter Field:=5
m = .ListColumns.Count
ReDim sc(m + 1)
For i = 1 To m
sc(i) = .ListColumns(i).Name
Next i
rr = .ListRows.Count
For t = 1 To rr - 1 'Delete all but one row
.ListRows(1).Delete
Next t
End With

For t = 1 To n
With Sheets(t)
If Not ss = .Name And .ListObjects.Count = 1 Then
For i = 1 To m
For j = 1 To ssc(t)
If sc(i) = .ListObjects(1).ListColumns(j).Name Then
.ListObjects(1).ListColumns(j).DataBodyRange.Copy _
Sheets("Quote Request
Summary").ListObjects(1).ListColumns(i).DataBodyRa nge.Cells(u)
End If
Next j
Next i
u = u + s(t)
End If
End With
Next t
With Sheets("Quote Request Summary").ListObjects(1)
.Range.AutoFilter Field:=5, Criteria1:="0"
End With
End Sub

Consolidating data entries across multiple worksheets to one s
 

Added notes
http://www.mediafire.com/file/ybfjzb...01_28_09b.xlsm