entirerow. copy

[kevinchurchill]

Hello all, I have a problem copying and pasting from one master sheet to its respetive heading sheet, I keep getting error 1004.
So for example in range R i have drop down boxes in each line, in these boxes i select for example production, quality, methodes ect...
So when for example i select Production and have filled out the row of information relating to a production proble, when I execute the macro i would like the row copied from the master sheet and pasted into the next available row in the Production sheet and the same goes for what ever heading I select from the dropdown list to there respective sheet.
here is a copy of the code that I have tried to use but not having any success with, I might add that the same code worked on a different spreadsheet with different headings but all the sheets were formated exactly the same as the master sheet all I have done is alter the names of the sheets.
Any help would be much aprieciated.

Sub CutData()


Dim PRODUCTION As String, Cell As Range, PRODUCTION_ As Range

'Production'

PRODUCTION = "Production"

For Each Cell In Range("R16:R200")

If InStr(1, PRODUCTION, Cell.Value, vbTextCompare) > 0 Then

If PRODUCTION_ Is Nothing Then

Set PRODUCTION_ = Cell

Else

Set PRODUCTION_ = Union(PRODUCTION_, Cell)

End If

End If

Next

If Not PRODUCTION_ Is Nothing Then


PRODUCTION_.EntireRow.Copy Destination:=Sheets("Production").Range("A16")

End If
End Sub

 

[mumps]

The copying can be done automatically upon selection from the drop down. It would be easier to help and test a possible solution if we could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[kevinchurchill]

Thank you for your early response,

https://www.dropbox.com/s/fu11kifxlmfbhg1/PDCA Chantier VAB.xlsm?dl=0

Here is the link that you requested, in the excel file you will see that on the first page pdca global, when i make an entry and then choose a catagory in column R, when I have finished entering data I would then like to activate the macro using the ENVOYER button that i created to send that data to its respective sheet instructed in column R, and then pasting the row to the next available line in its respective sheet.

So When I select for example Production from the dropdown box in R and have completed the entry in the row, when clicking ENVOYER it then sends the entire row to sheet named " Production" and pastes that data in the next available row, this needs to be the same for all catogories available in the drop down box using there respective sheets.
I hope this is clear. sorry that the data is in French but it wont change the structure.
Best regards.

 

[mumps]

Click this link to download your file: https://app.box.com/s/pxhxxujb7fya3szf1eab9eqw63hhj068
There were a few problems that came up. The first was due to the fact that your sheet name "Méthodes" has an "e" with an accent while in the drop down list in column R, "Methodes" has no accent on the "e" so I had to add the accented "e" in the drop down list. Secondly, the drop down list in column R of "pdca global", is different from the drop down list in column R of all the other sheets. Therefore, I could not do a simple copy/paste because that would cause the list in all the other sheets to be replaced with the one in "pdca global". I therefore had to do a copy/pastespecial and then pasting only the values. This then created a further problem because you have many merged cells. I was able to solve this problem by replacing the "pastespecial" with a ".value=.value" statement. I'm not sure if this makes sense so if you have any questions, please let me know. You should avoid using merged cells whenever possible because they create many problems for Excel macros. Rather than merging, you can simply widen the columns. In the "pdca global" sheet, make sure that you enter the data in all the columns other than column R first and make your selection in column R last. The data will be copied to the appropriate sheet automatically. You don't have to click the button which you actually no longer need. If you want to see the macro, it is in the worksheet code module. Right click the tab name for the "pdca global" sheet and click 'View Code". Close the code window to return to your sheet.

 

[kevinchurchill]

Hello Mumps,
I would like to thank you for taking your time to help me, what you have created is perfect for my needs.
The sheet works just as I wanted it to and also I can take time to learn from your code and from my mistakes, much appreciated.
Kevin

 

[mumps]

You are very welcome.