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?

 

[Akuini]

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?

Maybe like this:

For i = 1 To 5
Sheets("Sheet1").Range("B" & i & ":I" & i).Copy Destination:=Sheets("Sheet" & i + 1).Range("D39:K39")
Next

 

[Kamolga]

You can define 2 for is as the number of line (mayby last row in column B?)

Sub copy()
Dim i As Long
For i = 1 To 2
Worksheets(1).Range("B" & i & ":I" & i).copy
Application.Goto Worksheets("Sheet" & i + 1).Range("D39")
ActiveSheet.Paste
Next i
End Sub
[CODE]

 

[yinkajewole]

Wow! Amazing!

However, had it been I have renamed the sheets from "Sheet2" to "02" and so on... How do I effect it on the code?

 

[Akuini]

Wow! Amazing!

However, had it been I have renamed the sheets from "Sheet2" to "02" and so on... How do I effect it on the code?

You mean '02' not 'sheet02'?
If it is 02,03, ...,10,11..etc, then:

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

 

[yinkajewole]

This is interesting... KUDOS!

Sorry to bother you, is it possible to paste them without the formatting?

 

[Fluff]

If you want values only try

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

 

[yinkajewole]

If you want values only try

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

great!

Please, is it possible to convert this to an Add-in menu so that I can fill variables and run the code using buttons?

 

[yinkajewole]

I'm sorry about the way I asked the question. Let me express myself like this:
I want a this code to bring input box so that I will just insert the numbers/sheet in the code
For i = 1 To 15
Sheets(Format(i + 1, "00")).Range("D39:K39").Value = Sheets("Sheet1").Range("B" & i & ":I" & i).Value
Nexti.e. the 'i' will give me an input box to put '1' and '16'
so also the sheets and the range cells...

I hope someone could try to get me right... perhaps if I'm not right, corrections are welcomed.
which means I won't have to be going to the code editor changing the variables so as to avoid altering the codes unaware...

 

[Akuini]

I'm sorry about the way I asked the question. Let me express myself like this:
I want a this code to bring input box so that I will just insert the numbers/sheet in the code

i.e. the 'i' will give me an input box to put '1' and '16'
so also the sheets and the range cells...

I hope someone could try to get me right... perhaps if I'm not right, corrections are welcomed.
which means I won't have to be going to the code editor changing the variables so as to avoid altering the codes unaware...

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

Using the code above, what would become the variables?
1, 15, "D39:K39", "B", "I", "Sheet1"? All of it?
It would be easier if "Sheet1" just become the Activesheet.