Cannot paste to another worksheet in private sub change event

Hi,

I am trying to write a piece of code that is a private sub worksheet change event and I want to copy information from one sheet and paste it in another worksheet.

It wont even let me select a cell on the other worksheet - I presume that this is because it is a private sub it wont let me influence other workbooks.

Any ideas what I can do to remedy this - will making it a public sub make a difference or can I call the paste action from another macro? How would I go about doing this?

Thanks

Louise

Apologies, I got confused and meant to say workbook

Aaahhh I am having a mare with this one - I have tried using a copy destination syntax but it doesnt copy or paste anything:

Please Login or Register  to view this content.

Thanks Richard. Thanks so far Richard.

Essentially I want to write a worksheet change event that is triggered when column P in the prospects spreadsheet is set to y. The code then checks column m for the date and then I want to copy column a of that row onto the corresponding month on the pipeline spreadsheet, e.g. date 13/01 column a then gets pasted onto January. To add a complication to it, the macro needs to paste it on the first available blank row.

I will send over a copy of the spreadsheets when I am next at my computer, but I hope this helps to make things a bit clearer.

I attach the two files that I have been working on

Bump, no solution yet.

Thanks in advance for your help.

I have solved the issue with tweaking the following code - Yippeee!!!

Please Login or Register  to view this content.

Thank you so much for your help