Theoretical possibility question

[OleMiss2010]

This is a question possibility more than how to do something. Say I have a set of 10 rows. And I want those rows copied x number of times onto a worksheet. X is dependent on a number put into an input box.


Is it possible to write a VBA that can make x number of copies of a group of rows?

 

[vaskov17]

yes

 

[jeremymjp]

Absolutely. Check out "for" loops. Someone will paste in the actual code here shortly probably, and you'll be able to copy and paste that into the VBA editor, so for now I'll explain how you should probably think about this in order to begin writing your own code.


-You have a set of 10 rows. If it's always the same set of 10 rows, you can just define that with a named range in order to use it in the code.

-You want to paste those X number of times somewhere else. You'll need to get X from the user, so you'll need an input box. You'll also need to decide where to begin pasting the first copy of those rows. You can use an input box as well, or you can just decide for the user if it's going to be the same place every time. If option 2, you can use named ranges again to call a cell somewhere "BeginHere" (for example).


The code would be written as follows:

-Open the subroutine.

-Declare the variables you'll work with.

-Ask the user for the number of times the range needs to be pasted.

-Open a "for" loop to run X number of times to paste the range.

-Close the subroutine.

 

[madmarki]

the code to copy the cells is


Range("A2:A12").Select

Selection.Copy


and to paste them is


Range("A13").Select

ActiveSheet.Paste

 

[OleMiss2010]

Thanks guys. jeremymjp, I was basically thinking what you described. The sub is actually going to be a little more complex than I made it sound at first (I'm going to be asking about certain the number of times certain rows in the larger set should be repeated as well as how many times the larger set will be repeated). My only fear was that Excel wouldn't be able to handle this. This will be feeding into a worksheet so I'm thinking I'll have the vba create a second worksheet in the workbook to house the larger set while it's being created then I'll move the larger set over to the actual worksheet when it's finished.

 

[jeremymjp]

If you can imagine something logical and explain it step-by-step in English, so to speak, Excel can pretty much do it with VBA.

 

[RDAngelo]

To get you started...

[pre]

Sub copy_down()
'Copies your selection below your selection

Dim Message As String
Dim Title As String
Dim Default As Long
Dim MyValue As Long
Dim x As Long
Dim CopyData As Range

Message = "Enter how many times you want your selection copied"    ' Set prompt.
Title = "Copy Down Selection"    ' Set title.
Default = "1"    ' Set default.

' Display message, title, and default value.
MyValue = InputBox(Message, Title, Default)

Selection.Copy
For x = 1 To MyValue
Selection.End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Next x
End Sub

[/pre]