Excel vba problem help!!!

[effosia]

Hello,

I have combobox list with product options:
Z1
Z2
Z3 etc.

and I want to to repeat the same macro (copy and paste range) every time when sb choose product from the list.
Attached you will find a .xls with the recorded macro I don't know how to relate the functioning of the macro from the choosing of the value from Combobox

I know that I should use Select Case but I don't know how.
Thank you for your help in advance.

Here is the recorded macro:

Sub PROD_Z()
'
' PROD_Z Macro
'

'
    Columns("I:L").Select
    Range("I2").Activate
    Selection.Copy
    Columns("N:N").Select
    Range("N2").Activate
    ActiveSheet.Paste
    Range("Q1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Z2"
    Range("Q3:Q18").Select
    Selection.ClearContents
End Sub

[GazMathias]

Hi,

I don't quite understand what you are trying to do with your macro itself but if you are trying to pass the value from the combo box into the macro then:

Assuming you are using a combo box inserted from the Developer menu and assuming your values Z1,Z2 and Z3 are fed into the 'Input Range' property from a worksheet range then do the following:

Set aside two cells for later use, I shall refer to them as InputCell and ReturnCell.

1) Point your combo box's 'Cell Link' property at InputCell. This will put the index of the combo box into the cell when you change the selection.

2) In ReturnCell input the formula( =Index([the range for Z1,Z2, etc],InputCell). This will convert the number in InputCell into the real Z1, Z2, Z3 value.

3) Right Click the combobox and select Assign Macro.

4) Select your macro and click OK.

5) In your macro, read the value from ReturnCell:

Dim foo As String
foo = Range([ReturnCell]).Value2

If you put ReturnCell on a different sheet then adjust accordingly.

Then (if I understand what you are trying to do)

ActiveCell.FormulaR1C1 = "Z2"

Becomes

ActiveCell.FormulaR1C1 = foo

Gaz

P.S Think up a better name for your variable than foo!

[effosia]

Thank you for your reply.

I change the macro and now it looks like underneath:

If I click the button ADD PRODUCT it add products one time.
But I want to relate the functioning of the macro to the button.
Everytime when I click the button it will copy and paste range offset(0,5)
and add Z2,Z3,Z4... name in cell Q1(Z1), V1(Z2) etc.

Here is the code:

Sub CopyPaste()
'
' CopyPaste Macro
'
Dim i As Long
For i = 1 To 492
'Copies the specified selection to the Clipboard
Range("I:L").Select
Selection.Copy
'Paste the specified selection to the specified range
Range("I1").Offset(0, 5 * i).Select
ActiveSheet.Paste
'Unhidde the hidden columns
Selection.EntireColumn.Hidden = False
Next i
'Cancels Cut or Copy mode and removes the moving border
Application.CutCopyMode = False
End Sub

How to change the code to use by CommandButton.

Everytime when I click the button Add copied range to offset(0,5)
to max 492 times.
Now when I click the button Excel copy and paste range one time (492 times)

Thanks in advance.

[GazMathias]

So from what I understand you want:

A) a command to replicate the product blocks out to the right for each of the Z1, Z2 entries and you want each blue cell to contain the Z entry for that position in the index.

And

B) a command to add one new block to the end when you click it?

Gaz

[effosia]

Yes add new block and in blue cell add Z1 Z2 Z3 .. AHEAD until z492

everytime when I click the button copy the block and add next product name Z1
click
Z2
click
Z3
until
z492

[effosia]

When I click the button Copy the range I:M one time to right offset (0,5) and input in cell Q1 "Z1"
and click the button
Copy the range I:M one time to right offset (0,5) and input in cell V1 "Z2"

loop to x492 times

My macro do it but one time all replciation (x492) not one every time when I click the button

I hope this makes sense.

I'm new in VBA so I have no idea how to change my code to what I want to achieve.

[NeoPa]

Another similar thread was posted as EXCEL VBA copy paste loop macro.

Anyone should feel free to PM me directly if they see that a member has posted what amounts to the same question more than once so I can ensure one is removed before it gets too messy.

[effosia]

Please delete my threads as I found the solution.