Help with: If cells.Value = ws.Name then

[jng013]

I created a database in an Excel workbook. I named this workbook: "Model".
The sheets are named after a specific model in that sheet. For example, Worksheet("Model 5") contains data for our Model 5 product.

Each week, I get an Excel list of models that potential customers want to see. My goal is to run a macro that

1) Goes through each value on the customer's list
2) Compare this to the Workbook("Model") sheet names.
3) If there is a match between the cell value and sheet name, then copy data from that worksheet.
4) Then paste this data into the FinalRow of a separate worksheet.

I’m attempting to do this with a “for each” and loops. However, I am stuck at step 3. How do I word the code for it to copy data from that worksheet? Also, does anyone have a reference to an array code that performs a similar function?

I am using Excel 2007. My code at step 3 looks like this:
For each ws in worksheets
If cells(i, 1).Value=ws.Name, then ??

Thanks in advance.

 

[venkat1926]

some information is not available.
presume customer name is in column A in each worksheet
It is not known what is the name of the "separate" sheet. I called it "separate" modify this if you want.
I have also not understood FULLY your requiremnts

I have given a macro which is not tested.
if necessary modify it or tweak it.

Sub test()
Dim ws As Worksheet, lastcell As Range, i As Long
For Each ws In Worksheets
With ws
Set lastcell = .Cells(Rows.Count, "A").End(xlUp)
For i = 1 To lastcell.Row
If .Cells(i, 1).Value = ws.Name Then 'no comma before then
 .Cells(i, 1).EntireRow.Copy
  End If

With Worksheets("separate")
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With

Next i
End With
Next ws


End Sub

 

[jng013]

Howdy Venkat1926,

Thanks for the reply. Let me try to clarify myself.

If the customer's request matches a worksheet's name in the "Models" workbook, then copy the current region within that worksheet to a separate worksheet. There are about 45 different models to choose from.

For example: The customer requests to see Model 5 and Model 6. The macro should go into the Model's workbook, and copies the current region of worksheets named "Model 5" and "Model 6". Then paste this region into a separate workbook.

I do not want to copy the customer's list. How do I tell Excel to copy the current region of the matching worksheet? Do you see a better way to manipulate this data? Again, I just need help with the loop wording.

Thanks again!