Macro to copy value from one cell and paste it another cell

[msharma864512]

Hi,

I want to write a macro so that, It will pick the vales from the range A3 to A21 one by one, and then paste it in the Calculation Tab (Cell C3), when and copy the output from cell C6 (Calculation Tab), and paste the same in the range B3 to B21 of the Opportunity Tab.

First the formula should copy the value from cell A3 of the opportunity tab and paste it in Cell C3 of the calculation tab, and then it should copy the resulting value from cell C6 of the calculation tab to cell B3 of the opportunity tab,,,, and the same loop should run till Cell A21

 

[rollis13]

Could be a quick solution:

Option Explicit
Sub Macro1()
    Dim i      As Long
    For i = 3 To 21
        Range("A" & i).Copy Sheets("Calculation").Range("C3")
        Sheets("Calculation").Range("C6").Copy
        Sheets("Opportunity").Range("B" & i).PasteSpecial Paste:=xlPasteValues
    Next i
    Application.CutCopyMode = False
End Sub

 

[msharma864512]

Could be a quick solution:

Option Explicit
Sub Macro1()
    Dim i      As Long
    For i = 3 To 21
        Range("A" & i).Copy Sheets("Calculation").Range("C3")
        Sheets("Calculation").Range("C6").Copy
        Sheets("Opportunity").Range("B" & i).PasteSpecial Paste:=xlPasteValues
    Next i
    Application.CutCopyMode = False
End Sub

Hi Rolli13,

The macro is working perfectly fine, however there is just one issue with the code. The code should only copy the cells where there are values, and the code should stop the moment it finds a blank cell. For e.g. in the spreadsheet, I have data till A10, hence it should copy the value till A10, and post that it should be blank, and the code should not copy afterwards.

Regards,
Manish

 

[msharma864512]

Hi Rolli13,

The macro is working perfectly fine, however there is just one issue with the code. The code should only copy the cells where there are values, and the code should stop the moment it finds a blank cell. For e.g. in the spreadsheet, I have data till A10, hence it should copy the value till A10, and post that it should be blank, and the code should not copy afterwards.

Regards,
Manish

One more thing, the code is copying the exact data from column A, hence if there is any formula to calculate values in column A, the code is copying the formula as well, which is resulting in error. The code should only copy the values from the column A

Regards,
Manish

 

[rollis13]

It will pick the values from the range A3 to A21 one by one

I was following your request thinking you just needed a track, then you would've customized it by yourself.

Option Explicit
Sub Macro1()
    Dim i      As Long
    With Sheets("Opportunity")
        For i = 3 To 21
            If .Range("A" & i).Value = "" Then Exit For
            .Range("A" & i).Copy
            Sheets("Calculation").Range("C3").PasteSpecial Paste:=xlPasteValues
            Sheets("Calculation").Range("C6").Copy
            .Range("B" & i).PasteSpecial Paste:=xlPasteValues
        Next i
    End With
    Application.CutCopyMode = False
End Sub

 

[msharma864512]

I was following your request thinking you just needed a track, then you would've customized it by yourself.

Option Explicit
Sub Macro1()
    Dim i      As Long
    With Sheets("Opportunity")
        For i = 3 To 21
            If .Range("A" & i).Value = "" Then Exit For
            .Range("A" & i).Copy
            Sheets("Calculation").Range("C3").PasteSpecial Paste:=xlPasteValues
            Sheets("Calculation").Range("C6").Copy
            .Range("B" & i).PasteSpecial Paste:=xlPasteValues
        Next i
    End With
    Application.CutCopyMode = False
End Sub

Thanks Rollis,

Now its working fine! Thank you very much.

I tried customizing it from my end, but it was not working.

Regards,
Manish

 

[rollis13]

Thanks for the positive feedback, glad having been of some help.