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

jng013

New Member
Joined
Aug 4, 2011
Messages
2
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.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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.

Code:
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top