Copy & Paste Values to Same Cell, While Selecting Different Cell for Copy and Paste Values

Try this:

Option Explicit

Dim r&

Sub test()

  r = r + 1

  If r = 6 Then r = 1

  Cells(1, 2) = Cells(r, 1)

End Sub

Please add code tags (#) like this:

Sub Macro2()
    Range("B13").Formula = "='Data Template'!R[-10]C[6]"
    Range("B24").Formula = "='Data Template'!R[-21]C[1]"
    Range("C24").Formula = "='Data Template'!R[-21]C[-1]"
    Range("B26").Formula = "='Data Template'!R[-23]C[-1]"
    Range("B31").Formula = "='Data Template'!R[-28]C[2]"
    Range("B33").Formula = "='Data Template'!R[-30]C[3]"
    Range("B35").Formula = "='Data Template'!R[-32]C[5]"
    Range("B36").Select
End Sub

Now it's not clear what you want to do? Each time you click copy the contents to B1 (e.g. value in B13 then B24, etc)?

Sorry, still not completely understanding what you are after.

So the code above works because it brings the data from the other workbook to the current one. Then do you want to be able to cycle through those ranges as follows:

Option Explicit

Dim rng

Sub Macro2()

  Dim Rng, myRng

    Range("B13").Formula = "='Data Template'!R[-10]C[6]"
    Range("B24").Formula = "='Data Template'!R[-21]C[1]"
    Range("C24").Formula = "='Data Template'!R[-21]C[-1]"
    Range("B26").Formula = "='Data Template'!R[-23]C[-1]"
    Range("B31").Formula = "='Data Template'!R[-28]C[2]"
    Range("B33").Formula = "='Data Template'!R[-30]C[3]"
    Range("B35").Formula = "='Data Template'!R[-32]C[5]"
 
    myRng = Array("B13", "B24", "C24", "B26", "C31", "C33", "C35")

    If rng = 7 or rng = vbnullstring then rng = 0

    Range("B1").value = Range(myRng(rng)).value

    rng = rng + 1

End Sub

Hi,

Try this:

Option Explicit

Dim r&

Sub test()

  If r = 5 Then r = 0

    Range("B13").Formula = "='Data Template'!R[" & -10 + r & "]C[6]"
    Range("B24").Formula = "='Data Template'!R[-21]C[1]"
    Range("C24").Formula = "='Data Template'!R[-21]C[-1]"
    Range("B26").Formula = "='Data Template'!R[-23]C[-1]"
    Range("B31").Formula = "='Data Template'!R[-28]C[2]"
    Range("B33").Formula = "='Data Template'!R[-30]C[3]"
    Range("B35").Formula = "='Data Template'!R[-32]C[5]"
    
  r = r + 1

End Sub

It's really simple actually....

r is a number from 0 to 5. The first time you open the workbook and run the code it is automatically given a value of zero. Since r is put outside the 'sub' it will keep the last value even when the sub is complete. Therefore each time you run the sub it will begin from the last value that was saved.

Now as for the r being used in the formula, all it does is offset the first cell by r cells (e.g. 0, 1, 2...). Therefore the first time you run it the formula is -10 + 0... second time is -10 + 1... and so on.

Hope this helps.

abousetta

P.S. If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.