VBA script problem - Copy and Paste Special data from one sheet to another.

Dear all,

I would like to ask you for your assistance with the script I was given by another member of this forum. Long story short, I have a simple Excel database where I keep the records of all the sales made by our salespeople. In order to make the process or entering details about each sale made I have developed a simple UserForm which opens by pressing command button on the main Excel sheet i.e. "Data".

Here's a print screen of the actual UserForm:

User_form_Example.jpg

Sorry for the language, it is Russian, but it gives you some idea of what I am talking about.


Anyway, I have a problem with one VBA script which is meant to allocate all the data from the main worksheet "Data" to other 5 worksheets (which are named after our sales people), so that by clicking on a particular worksheet I would have a clear idea how many and what type of products that particular sales person has sold.

Here's the actual script:

Please Login or Register  to view this content.

If I leave it as it is and run the script, it automatically says that there is a problem with the following line of code:

Please Login or Register  to view this content.

However, if I change this line of code to the following (as shown below) it seems to work but to a certain extent.

Please Login or Register  to view this content.

The main problem is that whenever I run the script it automatically pastes the data (which has been entered though the UserForm) on top of the heading in a particular worksheet (depending on the name of the sales person which has been chosen in the UserForm), which is represented in Column G.


I am very sorry for the vague description of the problem, but I sincerely hope that it makes some sense and that it will allow you to help me out with this problem.


Thank you very much in advance. Any suggestion will be highly appreciated.


Sincerely,

Aleksandr K.

Hi Mike,

Thank you very much for your answer. I don't think that is the issue, because this script has to choose the worksheet based on the name of the manager who fills out the UserForm on the main worksheet "Data".

Here's the way it works.

1) The sales person opens Excel file and goes on the first worksheet names "Data".
2) On the left side (A1:B1) there's a command button "Jur_Form", which manager has to press and fill the pop-up form.
3) Once the form is filled in and submitted, it has to add all the details to the main worksheet named "Data" and should also allocate this data to the correct worksheet based on the name of the manager who was filling in the form i.e. "Aleksei", "Atis", "Andrei" etc.

P.S The thing is that the script which I have posted above seems to work just fine in my other Excel file (here's the link to it: http://d.pr/f/o6ci ) .

Sincerely,

Aleksandr Karev