Copy userform data to a second sheet based on ComboBox value?

[matrixpom]

Hi all once again,

Currently working on a code to be attached to a command button on a userform, I have the code to copy all fields to a specific sheet "AuditLog", however I also need the code to copy the same data to a second sheet based on the value in ComboBox1 eg. if ComboBox1 value is "CM1" then also copy data to sheet "CM1" in exactly the same way as originally done in "AuditLog" in next available row? Thanks to all again in advance & have a great day

Code so far...

Please Login or Register  to view this content.

[rylo]

Hi

try something like

Please Login or Register  to view this content.

then rerun the same action code.

you could put it in a loop (for i = 1 to 2) with an if statement

Please Login or Register  to view this content.

rylo

[matrixpom]

Hi rylo,

I have four alternate pages, ranging from "CM1", "CM2", "CM3" & "CM4", can you give me an example of the code with one of these, I've tried the above code & copied the action code but I keep getting a "run-time error '9': Subscript out of range with "set ws = worksheets(combobox1.value)"?

[rylo]

Hi

Can you put up the code that you tried.

rylo

[matrixpom]

Hi rylo,

I've altered it a few times & managed to get it to run but it still doesn't send the data to Sheet "CM1"? Thanks for your time rylo - Marco

Please Login or Register  to view this content.

[rylo]

Marco

Seems a bit out of wack. First you set the variable ws to the combobox1.value - OK. Then you check to see if the combobox really has been selected and if it hasn't then do a messagebox, but you don't set the ws variable if no selection had been made. Then you completely undo the ws variable by giving it the result of the unprotect.

If that doesn't make sense, then build an example workbook, with the 5 sheets, the form, the code and I'll have a look.

rylo

[matrixpom]

Hi rylo,

...my excel prowess is sadly way way below yours, any assistance you can give would be greatly appreciated, thanks again rylo! - Marco

Test1.xls

[rylo]

Hi

Nothing fancy here, so see how it goes.

Please Login or Register  to view this content.

rylo

[rylo]

Hi

and with a loop to process the data

Please Login or Register  to view this content.

rylo

[matrixpom]

Hi Rylo,

Works like a charm! If you're ever down the Goldie let me know, first shouts on me ok, cheers rylo!