VBA Input Box

[GCLIFTON]

Question: I need help. I am trying to write a VBA to Copy Set rng = Range("A1", "T189") 26, or 27 or 30 times by using the InputBox("Enter repetitions") and it is not working. All in one worksheet. Can anyone help me with that Code

 

[shg]

I don't understand that.

 

[Rick Rothstein]

Maybe ...

Sub copy()
  Dim rng As Range
  Dim i As Long
  
  Set rng = Range("A1:T189")
  For i = 1 To InputBox("Enter repetitions")
    rng.copy rng.Offset(rng.Rows.Count * i)
  Next i
End Sub

You can do that without using a loop...

Sub Copy()
  Dim Rng As Range
  Set Rng = Range("A1:T189")
  Rng.Copy Rng.Resize(Rng.Rows.Count * (1 + InputBox("Enter repetitions")))
End Sub

 

[shg]

Clifton, I leave you in Rick's capable hands.

 

[Rick Rothstein]

Clifton, I leave you in Rick's capable hands.

@shg,

Your hands are more than capable enough (all I was doing was pointing out a simplification). Anyway, I too do not understand Clifton's question from Message #10.


@Clifton,

What does Sheet2!A1:A26 have to do with the repeated Range A1:T189? And what value are you trying to "add" and where are you trying to add it at?

 

[GCLIFTON]

So Sheet 2 tells how many times the data will be duplicate. So i have in Sheet 2. Each one needs the copied data of 189 applied to it all in Sheet 1

[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]101[/TD]
[/TR]
[TR]
[TD="align: right"]102[/TD]
[/TR]
[TR]
[TD="align: right"]103[/TD]
[/TR]
[TR]
[TD="align: right"]104[/TD]
[/TR]
[TR]
[TD="align: right"]105[/TD]
[/TR]
[TR]
[TD="align: right"]106[/TD]
[/TR]
[TR]
[TD="align: right"]107[/TD]
[/TR]
[TR]
[TD="align: right"]108[/TD]
[/TR]
[TR]
[TD="align: right"]201[/TD]
[/TR]
[TR]
[TD="align: right"]202[/TD]
[/TR]
[TR]
[TD="align: right"]203[/TD]
[/TR]
[TR]
[TD="align: right"]204[/TD]
[/TR]
[TR]
[TD="align: right"]205[/TD]
[/TR]
</tbody>[/TABLE]

 

[GCLIFTON]

I forgot to answer the 2nd part of your question, Those units number above would just need to be applied in Cell U or any other cell behind T. So that there is a relationship to each copied data so i can eventually filter by Unit #

 

[GCLIFTON]

So Sheet 2 tells how many times the data will be duplicate. So i have in Sheet 2. Each one needs the copied data of 189 applied to it all in Sheet 1

[TABLE="class: cms_table, width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]101[/TD]
[/TR]
[TR]
[TD="align: right"]102[/TD]
[/TR]
[TR]
[TD="align: right"]103[/TD]
[/TR]
[TR]
[TD="align: right"]104[/TD]
[/TR]
[TR]
[TD="align: right"]105[/TD]
[/TR]
[TR]
[TD="align: right"]106[/TD]
[/TR]
[TR]
[TD="align: right"]107[/TD]
[/TR]
[TR]
[TD="align: right"]108[/TD]
[/TR]
[TR]
[TD="align: right"]201[/TD]
[/TR]
[TR]
[TD="align: right"]202[/TD]
[/TR]
[TR]
[TD="align: right"]203[/TD]
[/TR]
[TR]
[TD="align: right"]204[/TD]
[/TR]
[TR]
[TD="align: right"]205[/TD]
[/TR]
</tbody>[/TABLE]

 

[DocAElstein]

Hi GCLIFTON,

_It may be best if you go over to the Test Sub Forum and practice posting there first. Here is a Link to it:
Test Here
_ You can Post and Reply as much as you like there and make as many mistakes there as you like, - the Posts there are mostly ignored and deleted after a few days.

_ Try to achieve a good “Before” and “After” picture with reduced test data for both Sheets. Keep it in the form you did in Post # 17 ( See also the links in my signature for Guidelines and alternative ways to post a screen shot that we are able to copy to a Spreadsheet ( Do not post Images please as we cannot copy them ) )

_ Your "Before" should show how everything everywhere looks at the start before any code runs.
_ Your "After" should be filled in by hand by you to reflect exactly what you want the code to do based on all your “Before” data

_ Use just enough data to demonstrate your type of requirement: Normally any code we do for you would then work with little or no modification much more data ( Desensitize or make up your data, but chose it carefully so that all typical scenarios and formats can be considered ).

Alan

 

[GCLIFTON]

Thank you. Today is my first day. I will go to the Test Sub Forum and try that out. Thank you and i apologize if i have caused any trouble.