470,575 Members | 1,866 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,575 developers. It's quick & easy.

Excel vba problem help!!!


I have combobox list with product options:
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:
Expand|Select|Wrap|Line Numbers
  1. Sub PROD_Z()
  2. '
  3. ' PROD_Z Macro
  4. '
  6. '
  7.     Columns("I:L").Select
  8.     Range("I2").Activate
  9.     Selection.Copy
  10.     Columns("N:N").Select
  11.     Range("N2").Activate
  12.     ActiveSheet.Paste
  13.     Range("Q1").Select
  14.     Application.CutCopyMode = False
  15.     ActiveCell.FormulaR1C1 = "Z2"
  16.     Range("Q3:Q18").Select
  17.     Selection.ClearContents
  18. End Sub
Attached Files
File Type: xlsx CAT_Z.xlsx (31.6 KB, 144 views)
Jan 4 '18 #1
7 4467
228 Expert 128KB

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:

Expand|Select|Wrap|Line Numbers
  1. Dim foo As String
  2. 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)

Expand|Select|Wrap|Line Numbers
  1. ActiveCell.FormulaR1C1 = "Z2"

Expand|Select|Wrap|Line Numbers
  1. ActiveCell.FormulaR1C1 = foo

P.S Think up a better name for your variable than foo!
Jan 5 '18 #2
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:
Expand|Select|Wrap|Line Numbers
  1. Sub CopyPaste()
  2. '
  3. ' CopyPaste Macro
  4. '
  5. Dim i As Long
  6. For i = 1 To 492
  7. 'Copies the specified selection to the Clipboard
  8. Range("I:L").Select
  9. Selection.Copy
  10. 'Paste the specified selection to the specified range
  11. Range("I1").Offset(0, 5 * i).Select
  12. ActiveSheet.Paste
  13. 'Unhidde the hidden columns
  14. Selection.EntireColumn.Hidden = False
  15. Next i
  16. 'Cancels Cut or Copy mode and removes the moving border
  17. Application.CutCopyMode = False
  18. 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.
Attached Files
File Type: xlsx CAT_Z2.xlsx (44.8 KB, 145 views)
Jan 5 '18 #3
228 Expert 128KB
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.


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

Jan 5 '18 #4
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
Jan 5 '18 #5
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.
Jan 5 '18 #6
32,295 Expert Mod 16PB
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.
Jan 8 '18 #7
Please delete my threads as I found the solution.
Jan 8 '18 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by steve | last post: by
6 posts views Thread by samycbe | last post: by
6 posts views Thread by jtswim01 | last post: by
5 posts views Thread by Louis | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.