How to copy specific cell values in worksheet to another worksheet in the same workbook?

I hv a workbook with 3 sheets viz. OpgStk,Receipts,ClgStock
In OpgStk and Receipts sheets, I have columns for ItemName ("K") and ItemLotNumber ("J"). There are about 1000 ItemNames and for each ItemName there are about 15 ItemLotNumbers in OpgStk and Receipts Sheets. I need to copy the ItemLotNumber from both the OpgStk and Receipts Sheets in the ClgStock Sheet. I have tried with the following but not successful. Of course, I have not considered Receipts Sheet in this as I thought once I get through with OpgStk and I can proceed to Receipts on the same lines. Data start from B7 and ends at P15007, with row 6 for column headers.

Please Login or Register  to view this content.

Any help?

Code Tags Added
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(Because you are new to the forum, I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

I think this is the problem area

Please Login or Register  to view this content.

I think it should be

Please Login or Register  to view this content.

Are you getting an error code, and if so, which line is highlighted and the message you receive?

Did you click on Debug after getting the error message?

Look at this link. http://msdn.microsoft.com/en-us/libr...(v=vs.60).aspx

Try changing this line of code

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

and see if that works for you.

No luck........
Now the error message is 1004 (Application defined or object defined error)
There was an error in my code given to you and this has been modified as below:

Please Login or Register  to view this content.

I should say that col J in w1 is a calculated data, combining text values from two workbooks. I dont know whether this is causing any problem..
I tried through another route by auto filtering and copying method. The code is:

Please Login or Register  to view this content.

I get the very same Runtime error with message number 9. Debugging, w1.Range(.....) and w2.Range(....) shows ""Object Variable or With block variable not set".
The second method through filtering is not what I intended to do as it requires defining criteria range for n number of items. I want to be through with the first method.

I just want to tell that I am a novice to VBA and putting my efforts to learn it. I cant figure out how to overcome so many errors with my little knowledge.

I would be grateful for any help........Regards,

Don't have time to work on this tonight. Will try to evaluate tomorrow.