CODEWITHSUNDEEP
excelvba
Kev
Kev

Reputation: 25

VBA select values from one cell and put in another

I have my data on sheet 1 and my button macros on sheet 2. Basically when someone presses a button I want it to take a value in a cell in sheet 1 and populate the equivalent cell in sheet 2. for example Button 1 -> take value in sheet 1 cell A1 and put into sheet 2 cell A1 My current macro contains the actual value

Sub SelectCell()

Range("L4").Value = ("200")
Range("L5").Value = ("80")
End Sub

Upvotes: 2

Views: 640

Answers (2)

VBasic2008
VBasic2008

Reputation: 55038

Clone Values

  • s - Source, d - Destination

  • The code is to be copied to a standard module e.g. Module1.

  • It is assumed that you have a button on the destination worksheet, and whatever range you select on it, after pressing the button, the values from the source worksheet will be copied over. Multi-area ranges (e.g. "A1,C3,E12") are also covered.

The Code

Option Explicit

Sub cloneValues()

    Const sName As String = "Sheet1"
    Const dName As String = "Sheet2"
    
    Dim wb As Workbook: Set wb = ThisWorkbook

    If TypeName(Selection) = "Range" Then
        If Selection.Worksheet Is wb.Worksheets(dName) Then
            Dim drg As Range
            For Each drg In Selection.Areas
                drg.Value = wb.Worksheets(sName).Range(drg.Address).Value
            Next drg
        End If
    End If

End Sub

Upvotes: 1

snenson
snenson

Reputation: 436

First you should set your worksheets that you dont need to write it anymore. I asigned Sheet1 to ws1 and sheet2 to ws2.

Sub copy()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("Sheet1")
Set ws2 = ThisWorkbook.Worksheets("Sheet2")

ws2.Range("L4").Value = ws1.Range("L4").Value
'Loop from A1 to A100 and copy the values to sheet2
For i = 1 To 100
    ws2.Cells(i, 1).Value = ws1.Cells(i, 1).Value
Next
End Sub

Upvotes: 0

Related Questions