Hi Pete and Michelle

Roger - could you modify your code so that after it copied the six
sheets from the Prices file it inserted a new column C and
concatenated
A and B (Product and Batch) together?

I can if required, and I had played with using concatenation as a method
for Michelle to achieve her objective without resorting to VBA - which
would have been perfectly feasible.

With the VBA solution, however, she has all of the data she needs
without having to resort to any further Lookups of any description hence
I do not really think it is required.
Marking the top row and applying DataFilterAutofilter will permit the
selection of any range of products, all of which will have their
Description and Price.

I have noticed 2 errors in my original code
Set Spath = "C:\Excel\Ngroups\" ' <=== Change to suit
Should not have the word Set at the beginning, it should just read

Spath = "C:\Excel\Ngroups\" ' <=== Change to suit

Also, I can get inconsistent results with the line
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
This should be amended to
lastrow = DestSheet.Cells(Rows.Count, 1).End(xlUp).Row

I have repeated the complete code below, with the inclusion of Pete's
request for the concatenation.
I have remmed out the 2 sections which have been added for this purpose,
removing the single quote in front of the lines where shown will cause
them to be invoked.

Sub ColateData()
Dim Wbook As Workbook, Spath As String
Dim SourceSheet As Worksheet, DestSheet As Worksheet
Dim addrow As Long, lastrow As Long
Dim source As Range, dest As Range

Application.ScreenUpdating = False
ThisWorkbook.Sheets("Summary").Select
Set DestSheet = ThisWorkbook.Sheets("Summary")
With DestSheet
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
If lastrow = 1 Then lastrow = 2
Rows("2:" & lastrow).Clear

'Remove single quotes at beginning of next 3 lines if Concatenation of
'Product and Batch is required

'If UCase(Range("C1")) < UCase("Description") Then
'Columns("C:C").Delete
'End If

End With
Spath = "C:\Excel\Ngroups\" ' <=== Change to suit
Set Wbook = Workbooks.Open(Spath & "Prices.xls") ' line amended to
remove Set

For Each SourceSheet In Wbook.Worksheets
With DestSheet
lastrow = DestSheet.Cells(Rows.Count, 1).End(xlUp).Row ' Destsheet added
to line
End With

addrow = SourceSheet.Cells(Rows.Count, 1).End(xlUp).Row
Set source = SourceSheet.Range("A2:D" & addrow)
Set dest = DestSheet.Range("A" & lastrow + 1)
source.Copy dest
DestSheet.Range("E" & lastrow + 1 & ":E" & lastrow + addrow - 1)
= SourceSheet.Name

Next

'Remove single quotes at beginning of next 3 lines if Concatenation of
'Product and Batch is required
' DestSheet.Columns("C:C").Insert
' DestSheet.Range("C2").FormulaR1C1 = "=RC[-2]&""|""&RC[-1]"
' DestSheet.Range("C2").Copy DestSheet.Range("C3:C" & lastrow +
addrow - 1)


Wbook.Close Savechanges:=False
ThisWorkbook.Save

Application.ScreenUpdating = True
End Sub

Post back if you have any difficulties.


--
Regards

Roger Govier


"Pete_UK" wrote in message
oups.com...
Hi Michelle and Roger,

sorry for not responding sooner - have been getting ready to go away
first thing in the morning.

Roger - could you modify your code so that after it copied the six
sheets from the Prices file it inserted a new column C and
concatenated
A and B (Product and Batch) together?

Michelle - if Roger joins the two fields together in a new column C of
your Summary sheet, then assuming there are no duplicates you can
enter
these formulae in columns C and D of your Estimates sheet:

C2: =IF(ISNA(VLOOKUP(A2&B2,Summary!C:F,2,0)),"Not
Present",VLOOKUP(A2&B2,Summary!C:F,2,0))
D2:
=IF(ISNA(VLOOKUP(A2&B2,Summary!C:F,3,0)),"",VLOOKU P(A2&B2,Summary!C:F,3,0))

and you can add a column E with "Brand" in E1:

E2:
=IF(ISNA(VLOOKUP(A2&B2,Summary!C:F,4,0)),"",VLOOKU P(A2&B2,Summary!C:F,4,0))

If a match isn't found you will get the message "Not Present" in
column
C and blanks in D and E, otherwise you will get the Description, Price
and Brand returned in these columns. If there are duplicates, then
VLOOKUP will find the first of these. Copy the formulae down for as
many entries as you have in A and B.

Hope this helps.

Pete