Run macro row by row & slightly adjusting the macro itself

[viktoriia]

Hi all,

I'm very new to macros in excel and would hugely appreciate any help I will try to outline my 'issue' as much I can below, but please feel free to let me know if I should elaborate on something or make it more 'readable'.

I have to build a pretty simple macro, which I keep failing to construct.

Short background: I want 3 values on one row in different columns (e.g. S3, T3, V3) from one sheet (e.g. Sheet 1) be pasted to another sheet (Sheet 2), where some calculation will be done, and the result of it calculation, which is a single cell (e.g. B13) should be copied back to Sheet 1; I have to explain to excel somehow that it applies this macro row by row within a specific range.

Here is my short code I have thus far and I would like to keep the values in bold within asterisks dynamic.

Sheets("Sheet1").Range(**"S3"**).Copy
Sheets("Sheet2").Range("B12").PasteSpecial xlPasteValues
Sheets("Sheet1").Range(**"T3"**).Copy
Sheets("Sheet2").Range("B15").PasteSpecial xlPasteValues
Sheets("Sheet1").Range(**"V3**").Copy
Sheets("Sheet2").Range("B19").PasteSpecial xlPasteValues
Sheets("Sheet2").Range("B13").Copy
Sheets("Sheet1").Range(**"AE3"**).PasteSpecial xlPasteValues

So basically in the next row the code should be like follows

Sheets("Sheet1").Range(**"S4"**).Copy
Sheets("Sheet2").Range("B12").PasteSpecial xlPasteValues
Sheets("Sheet1").Range(**"T4"**).Copy
Sheets("Sheet2").Range("B15").PasteSpecial xlPasteValues
Sheets("Sheet1").Range(**"V4**").Copy
Sheets("Sheet2").Range("B19").PasteSpecial xlPasteValues
Sheets("Sheet2").Range("B13").Copy
Sheets("Sheet1").Range(**"AE4"**).PasteSpecial xlPasteValues

But unfortunately, I keep failing to bring it to work. I think it should be pretty easy doable as the logic behind is quite simple, but since I'm new to VBA I don't know yet how to explain it to excel.

Any hints/tips are very valuable!

Cheers and keep safe
Viktoriia

 

[Joe4]

Welcome to the Board!

You can loop through the rows.

So you could replace this:

Sheets("Sheet1").Range("S3").Copy
Sheets("Sheet2").Range("B12").PasteSpecial xlPasteValues
Sheets("Sheet1").Range("T3").Copy
Sheets("Sheet2").Range("B15").PasteSpecial xlPasteValues
Sheets("Sheet1").Range("V3").Copy
Sheets("Sheet2").Range("B19").PasteSpecial xlPasteValues
Sheets("Sheet2").Range("B13").Copy
Sheets("Sheet1").Range("AE3").PasteSpecial xlPasteValues

Sheets("Sheet1").Range("S4").Copy
Sheets("Sheet2").Range("B12").PasteSpecial xlPasteValues
Sheets("Sheet1").Range("T4").Copy
Sheets("Sheet2").Range("B15").PasteSpecial xlPasteValues
Sheets("Sheet1").Range("V4").Copy
Sheets("Sheet2").Range("B19").PasteSpecial xlPasteValues
Sheets("Sheet2").Range("B13").Copy
Sheets("Sheet1").Range("AE4").PasteSpecial xlPasteValues

with this:

Dim r as Long
For r = 3 to 4
    Sheets("Sheet1").Range("S" & r).Copy
    Sheets("Sheet2").Range("B12").PasteSpecial xlPasteValues
    Sheets("Sheet1").Range("T" & r).Copy
    Sheets("Sheet2").Range("B15").PasteSpecial xlPasteValues
    Sheets("Sheet1").Range("V" & r).Copy
    Sheets("Sheet2").Range("B19").PasteSpecial xlPasteValues
    Sheets("Sheet2").Range("B13").Copy
    Sheets("Sheet1").Range("AE" & r).PasteSpecial xlPasteValues
Next r

 

[BSALV]

try Offset, with that one you can shift in the rows and in the columns.
How many rows, that you can say in many ways, here for example with the value of cell A1

Sub MyOffsetPaste()

     Set sh1 = Sheets("sheet1") 'set with a shorter name
     Set sh2 = Sheets("sheet2")

     i = sh1.Range("A1").Value 'via A1 of sheet1, you know the offset, is an integer and you start with 0, then 1, ...
     
     With sh2
          .Range("B12").Value = sh1.Range("s3").Offset(i)
          .Range("B15").Value = sh1.Range("T3").Offset(i)
          .Range("B19").Value = sh1.Range("V3").Offset(i)
          .Range("B13").Value = sh1.Range("AE3").Offset(i)
     End With

End Sub

 

[viktoriia]

Amazing guys! huuuuuge thanks to you for an immediate reply

It works perfectly ?
And the solutions you've provided are so understandable.

Thanks again a lot!

 

[Joe4]

You are welcome.
Glad we were able to help!