Excel vba increment cell and sheet selection

[yinkajewole]

I do run this VBA code

Sub sbCopyRangeToAnotherSheet()


Sheets("Sheet1").Range("B1:I1").Copy Destination:=Sheets("Sheet2").Range("D39:K39")
Sheets("Sheet1").Range("B2:I2").Copy Destination:=Sheets("Sheet3").Range("D39:K39")

End Sub

i.e. changing the range "B1:I1" and sheet "Sheet2" incrementally.

Is there a way the run the code for these portions incrementally without entering the code line by line manually?

 

[yinkajewole]

ok... so you mean, it wouldn't work if it is going to pasted into other sheets?

 

[Kamolga]

It does not change anything if you paste to another sheet. We just don't understand exactly why to try to do. Can you give an example with whatever number so like "User comes on sheet "Data", press a button, comes a message box where he chose between 1 and 16. He types 3 then...

By the way if you want to change i with an input box

Dim i as variant
i=inputbox("Give me a number between 1-16")

 

[yinkajewole]

//code
For i = 1 To 15
Sheets("Sheet1").Range("B" & i & ":I" & i).Copy Destination:=Sheets(Format(i + 1, "00")).Range("D39:K39")
Next

 

[yinkajewole]

[COLOR=#333333]For i = [B]1[/B] To [B]15[/B][/COLOR]Sheets("[B]Sheet1[/B]").Range("[B]B[/B]" & i & ":[B]I[/B]" & i).Copy Destination:=[B]Sheets(Format(i + 1, "00"))[/B].Range("[B]D39:K39[/B]") [COLOR=#333333]Next[/COLOR]

What I really wanted is that, whenever I should run the code, it should give my an InputBox to put in the above bold numbers, sheet and range

 

[Kamolga]

[COLOR=#333333]For i = [B]1[/B] To [B]15[/B][/COLOR]Sheets("[B]Sheet1[/B]").Range("[B]B[/B]" & i & ":[B]I[/B]" & i).Copy Destination:=[B]Sheets(Format(i + 1, "00"))[/B].Range("[B]D39:K39[/B]") [COLOR=#333333]Next[/COLOR]

What I really wanted is that, whenever I should run the code, it should give my an InputBox to put in the above bold numbers, sheet and range

That is not an input box that you need but a userform...

 

[yinkajewole]

That is not an input box that you need but a userform...

Well, in that case, if that is what I need, how do I go about so I can give it a try?

 

[yinkajewole]

please, who will help me out in creating the userform as i have been trying but all to no avail?

 

[yinkajewole]

can anyone refer me to any thread or tutorial that can help me with the userform?

 

[Kamolga]

I started with youtube (https://www.youtube.com/results?search_query=userform+beginner) and googled very specific questions such as "Userform textvalue to cell" when I was blocked.

If too complicated, you can just set up inputbox launched by button (put a few buttons), or a macro launching several inputboxes one after another...but a userform with a few textbox and an ok button is quite easy to set up (unfortunately difficult to explain in a thread).

 

[yinkajewole]

I started with youtube (https://www.youtube.com/results?search_query=userform+beginner) and googled very specific questions such as "Userform textvalue to cell" when I was blocked.

If too complicated, you can just set up inputbox launched by button (put a few buttons), or a macro launching several inputboxes one after another...but a userform with a few textbox and an ok button is quite easy to set up (unfortunately difficult to explain in a thread).

[COLOR=#333333]For i = 1 To 15[/COLOR]
[COLOR=#333333]Sheets("...").Range("..." & i & ":..." & i).Copy Destination:=Sheets(Format(i + 1, "00")).Range("....:...")[/COLOR]
[COLOR=#333333]Next[/COLOR]

but i don't seem to find the one that can easily complete some lines of vba code..