EXCEL VBA copy paste loop macro

[effosia]

Hello,

I need a simple macro to copy range ("I:M") and paste offset(0,5) every time when I click the button.
max 492 times

Thanks in advance for any help

[NeoPa]

When you say it might need to be repeated I find myself confused.

1. In what way would repeating the action make any sense?
2. Does the limit specified refer to the number of times the code can be invoked by some trigger or is there some looping required?
   If the latter then what determines the looping?
3. Does the Offset() you're talking about reference the Range() object mentioned or would this be from Selection (You should appreciate that as a Method, Object() has no meaning unless qualified.)?

Do please try to consider your question fully before posting it.

[effosia]

I need to copy Product Table x492 times.
Every time when clicking the button add next table and add name Z1, Z2,Z3 etc.

In my macro Excel add the copied range once all x492. I want to depend functioning of the macro from the button ADD PRODUCT.
or Combobox list
If sb choose Z1 from the list then will see the table to put the data
If choose Z2 will see the next table to input data for product Z2 etc.

The loop determines clicking on the button Add Product.

[effosia]

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

    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)

[NeoPa]

I see no answers to my questions in there but I'll look again when I have more time and see if I can work out what you're saying. I expect the code will give me some sort of an understanding.

PS. Each question thread has only one question. Please do not try to add extra questions in here. It makes very little sense anyway as you haven't asked the first in a way that makes sense yet. Jumping forward when we haven't even got started is no way to make progress.

[effosia]

1) User have x492 product and want to add data of each product.
Not to copy and paste product table each time manaly
macro will copy and paste
2) I need trigget (the button) to active the macro each time. Every time when click copy the range with Range("I1").Offset(0, 5 * i)
Max times to click x492
3) Offset reference the Range()

[effosia]

Please for any help as I need it to my assignment, the deadline is 07/01.

[GazMathias]

@NeoPa

This question is posted across two active threads. Is there anyway of merging them?

@effosia

I have looked at your spreadsheet and for your single code copy function I feel that you are missing an essential piece of information against your Z entries to signify whether or not they have been been copied to the blue cells.

Please consider adding a column that uses index and match (or equivalent) to track whether or not the value exists in the blue cells. Also consider using a single tracking cell whose job is to return the next number to copy. Look at Excel's Max() function.

You would retrieve this number in the single code copy function and use it to calculate the position of the blue cell to copy the value in to by multiplying it. You seem to be using those kinds of commands already.

You could do this in pure VBA but I feel this approach will help you in the long run as helper columns and tracking cells are essential in more complicated projects in my opinion.

The copy block 492 times you can accomplish by simply thinking about the steps required and going from there. You have most of what you need already.

Gaz

[NeoPa]

Hi Gaz.

PM me a link to the other and I'll see what I can do.

As this is clearly an assignment, which as we all know is not allowed here, I will make it very clear that posting help is going to be allowed but anyone posting the solution for the OP is likely to have their post deleted and get a warning for breaking the rules.

@Effosia.
I'm not happy that you've made such a poor job of asking the question. I'm even less impressed that you should do so when someone's already provided you with the assignment. I assume whatever you were given to do already made sense. Assignments are not acceptable questions. Asking about a particular concept that you need in your assignment we'll generally help with though. It needs to be asked a lot more clearly than you have done though.

-Adrian (Admin).

PS. @Gaz.
What you've done so far seems perfectly acceptable.

[effosia]

It was a little part of my assignment. Close I found the solution.

[NeoPa]

Another similar thread was posted as Excel vba problem help!!! (I know, but we have no control over what people use as titles even though there are clear guidelines.

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.

[NeoPa]

That's not how it works Effiosa. Threads typically aren't deleted unless they're a problem. Tidying up yours would be too much work to be worth the effort so they'll stay as they are.