copy different data via users choice

**_Terry_** · 07-05-2008, 01:37 PM

Hi all

I currently have data on sheet 3 that I'd like to be copied to 'sheet 1 D4' but where it gets copied from depends on the users choice.

Sheet 3 has blocks of data starting at A1 in blocks of 10x10, so block 1 is at A1, block 2 is at L1 etc,. allowing 1 column between blocks to show seperation.

It generally would work that on sheet 1 the user gets the choice of currently 16 blocks of data (which will be increasing) on sheet 3, then once chosen that specific block of data gets copied to sheet 1 D4

The last time I tried something similar I ended up creating a macro for every block and then adding a command button on sheet 1 per block of data, so sheet 1 looked rather full boxes for the user to click, which turns into quite a long process when the data builds up.

What type of setup would be better suited to this type of query?

Cheers all

**MickG** · 07-05-2008, 04:17 PM

Hi, You can do it all by selecting from a "Combobox"'
Place the following in Sheet(3).range "A15:A31" (Change Later to suit)

Please Login or Register  to view this content.

Place a "Combobox" on Sheet (3), from the "View", "Toolbars", "Control Toobox", Menu.
Right click the "ComboBox" in Edit mode choose "Properties"and insert against the "LinkfillRange" the range:- A15:A31.
The "comboBox" should now be full of Block Numbers.

Right click the "Combobox" in Edit Mode, and select from the menu "view code".
Clear the event Handling text from the VB Window,and Paste the code below , into window, ensuring its in the ComboBox1_click event as shown.

Select a "Block Number" from the "ComboBox" and the Appropriate Range should be copied to sheet(1) Range "D4"

Please Login or Register  to view this content.

Hope I haven't forgotten anything
Regards Mick

**_Terry_** · 07-06-2008, 07:37 AM

Wow, that's why I've never been able to get a combo box to work LOL

I've followed your instruction but when I run it I get a compile error which highlights the very last 0 of the last line.

Any thoughts?

Big thanks though for looking at my query, much appreciated.

also I notice that you used split cell, I'm not familiar with that one, how does that work?

**royUK** · 07-06-2008, 08:42 AM

Try this way using the combobox. It uses the Combobox's listindex to determine the range to copy.

**MickG** · 07-06-2008, 09:58 AM

Hi Terry
I've recreated the Worksheet, ComboBox and Code by copying it back from the Forum thread, but unfortunately, I can't replicate your error.

Just to recap:-
Make sure your "ComboBox" is in sheet(3).

The first Item in the ComboBox should be "Select Block For Pasting" ??

The Line with the "Error" :-"Sheets("sheet3").ComboBox1.ListIndex = 0", is there so that after you've chosen a "Bock Number" the ComboBox Text defaults to the words "Select Block for Pasting".You could try commenting it out for a start see if it makes a difference. The code will still work.
To find out what value the ListIndex (0) is:-
Above the "Error" line, insert the following:-

Please Login or Register  to view this content.

See what it says !! It should be "Select Block for Pasting"

I think I would also try removing the code, make sure all the items Function Properly in the "ComboBox" then Replace the code.

The "Split" Function enables you to split a string at certain points and converts it to a one dimensional Array.
In this particular case by adding the Subscript (1) it returned a column Letter, like "A" from an Address "$A$1", which is quite useful.
Has a look at the VB Help.

If you can't get the code working properly, call back with any further details.
Regards Mick

**_Terry_** · 07-06-2008, 10:32 AM

Just tried your last suggestion with the MsgBox but the same thing happened.

Had another look at the code and got it sorted, it simply needed an End Sub added as the last line, sorry, that maybe my limited VBA knowledge that should have picked this up

Great stuff and does the job.

Big thanks.

I've also saved a copy of the zip file to learn an alternate way to process, good stuff.