Copy data when selecting a drop dwon seletion

[stanton17]

Hi

Can anyone help me with a bit of code. Im trying to copy a selection of data from a seperate workbook and paste it into my active sheet when the user selects certain Month from a drop down list. This is how far i have go but i can't get it to work.

Please Login or Register  to view this content.

[Olly]

Try:

Please Login or Register  to view this content.

This allows you to specify the source workbook, worksheet and range for each value in your dropdown.

[stanton17]

Olly

Thank you for this, However i cant seem to get this code to work. When i select 'September' from the dropdown nothing happens. Its need to open a file path then copy certain cells to the orignal workbook

[Olly]

Where did you put the code?

Attach your workbook, please.

[stanton17]

I assigned the Macro on Dropdown4, in Module1.

I have done a simmple copy of my workbooks as i cant attached the origanls.

[Olly]

Okay, change your code slightly, to this:

Please Login or Register  to view this content.

[stanton17]

cheers i will check this out tomorrow. my next step will be to put filepaths in for the rest of the Months

[stanton17]

This works good thank you. I also want to copy data from other workbooks into other cells. how would i amend the code?

[Olly]

Try this:

Please Login or Register  to view this content.

For each value in your dropdown, change the relevant values in the "Case " section, to indicate the source file, source range, and target range.

[stanton17]

Thank you. I have managed to link up all the months now. The next problem i have is that i need to pull data not just from one workbook and place for every month. could a just duplice the code below?

Please Login or Register  to view this content.

Thanks for all your help with this

[Olly]

Thank you. I have managed to link up all the months now. The next problem i have is that i need to pull data not just from one workbook and place for every month.

Sorry, I don't understand this. What are you trying to do?

[stanton17]

sorry i dont think i explain it very well. At the moment when selecting a month, say september the code looks at workbook stanton17_sep.xlsm and then copies E6:I10 from sheet1

What i also need to do is at the same time is look at other wookbooks say 'C:\temp\stanton17_sep_workbook2.xlsm' and copy A1:A10 from sheet1 and do that for as many workbooks i want.

The reason is i have data on several different workbooks i need to pull into the sheet with the month selector on.

[Olly]

Is it a fixed number of workbooks per month selection?

[stanton17]

Yes its 7 workbooks every month. The workbook with the month selector is a Dashboard, a collection of data from the 7 workbooks

[Olly]

And is it 7 different workbooks for each month?
Or is it the same 7 workbooks, but a different RANGE for each month?

[stanton17]

hi its 7 different workbooks for each month

[stanton17]

When a month is selected then the data need to put from 7 different workbook and 7 different ranges at the sametime into different cells on the dropdown sheet. Then when you select a different month it will pull data from worksheets called the next month.

I hope you understand this

cheers

[stanton17]

Hi its 7 different workbooks for each month. there is upto 7 spreadsheet people complete that i need data to feed into the month selector workbook, then the next month the 7 spreadsheets will be copied and renamed the next month. I hope this makes sence?

Many thanks

[Olly]

It's quite hard to make efficient code without understanding your source workbook structure.

Worst case is there are 84 workbooks, each with a unique name, worksheet name, source range and target range. So, I could code it to define 84 x 4 bits of information, and loop through the relevant 7 x 4, based on the selected month...

I bet there's a more efficient way, though - I bet you have some consistent naming conventions, some consistent source and target ranges... so I bet we don't actually have to specify 84 x 4 bits of source information. However - without an understanding of your actual structures and names, it's impossible to work this out...

See the attached workbook - it contains a single table, to map out your import specification. If you can complete this table, then we can look at the most efficient way of importing the data, from the 84 source files.