Auto Population

Ok, so this is my first attempt at an excel macro. I'm trying to develop a macro that will assist me in auto-populating a column in a new worksheet based on another worksheet. Recently I ran a new report at work that is 4000 lines long that I really do not want to have to edit by hand, since I already had to do it with the older one.

I can't post my data but i'll try to explain it the best I can, but basically it's set up as a part number, next column is the steps in completing that part, and then a workcenter number. I'm trying to populate that workcenter column in the new worksheet. I've put this macro together with the best of my ability along with excel help and the internet but I just can't seem to get it to work.

Please Login or Register  to view this content.

When I step through it it seems to fail at the line Range("A1").Select and I'm not sure why, any advice would be appreciated, or if my code is completely wrong and tips to point me in the right direction would be appreciated.

Thanks in advance,
J

Try removing this line:

Please Login or Register  to view this content.

You shouldnt have to go to cell A1 to perform a find in excel.

All of the ".Select" could be ".Activate". You really do not need to select cells.

Can you attach a sample workbook!?

Is there a worksheet missing?
"SFC times by plan"

Which sheet is the starting sheet?
SFCTimesOld or SFCTimesNew

Just an FYI
part of the problem is this portion of code:

Please Login or Register  to view this content.

Once you get to the end of the list of values, there will not be any match to varOperNo. The loop will continue to the last row, where you try to offset to a row that doesnt exist.

The find is finding the value, the code is backwards.

Try this code: It searches the usedrange, not the entire 16,384 rows.

Please Login or Register  to view this content.

This message box

Please Login or Register  to view this content.

shows the address of the "pnfind" variable.

This leads to the next error.

Please Login or Register  to view this content.

The code in Red will fail because there is no value 6 rows down.

Perhaps you can explain what needs to be copied from which cells to which cells.
Seems like you are changing worksheet row/column formats (headings etc.)?

Deleted text

Are you wanting to copy data by Part Number, Operation Number and Work Center (only those cells) to the new sheet, or all data from sheet1 to sheet2 into relative cells?
Eample:
You have 8 operations on sheet1 and 10 operations on sheet2 for part number 12345.
Thus do you want to copy just work center value to sheet2 only if there is a matching operation or all relative data including all operations?

Excuse the time delay's, I am looking at this between my projects.
If its any consolation, I am in manufacturing as well.

Ok, lets give this a try.

Notes:
The macro works on a "Used Range". Any errant data beyond Column N may cause unforseen problems.
Color coding is a visual aid for programming. It is not needed.
Duplicate worksheets (2) respectively, were to cover my behind incase I screwed it up.
I added a "Status Bar" text message (That’s the lower left hand corner of the Excel wondow, typically shows the word "Ready".
Let me know how the status bar works on large data. It appears to work fine on this small amount of data.

Instructions: (pertains to this workbook only)
Click on the CommandButton at the top of the worksheet.
Switch between worksheets to verify data transfers.

HTH

Man, I hope I got this right, I was popping brain cells by the bazillions. LOL

Just make sure you run the macro on "copies", proof read some of it, then go ahead and use it.

Glad I could help.