VBA copy paste from drop down ( multiple rows and columns ) into second workbook

[givemeaccessexcel]

Hi, I am trying to figure out how to copy and paste multiple rows ( choose columns ) from one workbook to another, if I choose a variable from a list of drop down menus. Example is attached.

Workbook 1, is called "Sample Request" ( attached ), and in the spreadsheet "DATA", there are 5 columns - "Country", "Animal", "Gender of Animal", "Owner" and "Date of Birth". The drop down menu is in cell H1. The named range is "country". So if I choose a country in H1, I want the code to copy and paste all the data under headings: "Animal", "Gender of Animal" and "Date of Birth" ( Not "Owner" ) into the spreadsheet "Report" in the second Workbook, called "Output".

In the second Workbook "Output", in the spreadsheet "Report", each heading "Animal", "Gender of Animal" and "Date of Birth" is duplicated. The reason why there are two columns of each, is because I want to incorporate a conditional formatting code ( which I already have ) that identifies when the user changes the information that is in either of these columns ( the second/duplicate column is hidden - so user only sees one version ).

Would anyone be able to help me create the code above? Much appreciated

[mjr veverka]

What about the country and to which columns to copy the source data for example 'Animal' to 'C' or to 'D' (output.xlsx) ?

[mjr veverka]

Ok, without complaints ... ... try:
File "output.xlsx" copy to "C:\Temp" (for testing)

ThisWorkbook module:

Please Login or Register  to view this content.

Sheet "DATA" module:

Please Login or Register  to view this content.

Standard module:

Please Login or Register  to view this content.

plus corrected name defined.

[givemeaccessexcel]

thank you for your quick response! I have opened the macro you've uploaded (outside the zip) and tried to run it by typing "unlocked" in cell L1, and choosing a random country (e.g. canada) in H1. However, I get an error message with the standard module (module 1) - the code being:

Please Login or Register  to view this content.

I'm not sure what to do to fix this - could you please advise? I tried to change the outputPTh name to something other than the temp folder but it didn't fix it.

[mjr veverka]

In line:

Please Login or Register  to view this content.

change "output.xls" to "output.xlsx"

Please Login or Register  to view this content.

I tried on the old excel and that's how it remained in the code.
This three-line section is for you, to change data according to your requirements

Please Login or Register  to view this content.

[givemeaccessexcel]

Thank you, I will try this and let you know how I go.

[givemeaccessexcel]

Hi porucha vevruku, I still can't seem to get this to work. I changed the extension. The same debugging error occurs.

Please Login or Register  to view this content.

[mjr veverka]

What error occurs, what number, what description ?
Make a screenshot maybe.

[givemeaccessexcel]

Debugging Error 1.PNG

Debugging Error 2.PNG

[mjr veverka]

1. What is selected in 'Tools\References' (vba editor, Alt + F11) ?

2. Replace the macro in the standard module with the following:

Please Login or Register  to view this content.

Something has changed or not ?

[givemeaccessexcel]

1. The following are selected in tools\references
Error3.PNG

2. I have copied and pasted it to the standard module, and this time I get this error:
Error 1.PNG

After updating coping and pasting the code into the standard module, when I try to save, I now get a privacy warning message below - is this normal?
Error2.PNG

[mjr veverka]

A privacy warning message is a normal thing, you can change it:
https://support.office.com/en-us/art...0EAACAAA=Excel
https://superuser.com/questions/3775...rning-in-excel

Try this file - old excel file ( password for zip: Bird ):
If it does not work, I have no idea what's going on.

[givemeaccessexcel]

Thanks, unfortunately, the same runtime error 432 occurs (post response #9).