How to Copy Value from another Sheet

If penile arteries are clogged, affecting erection, then such pill is Kamagra, which helps you boost your flow of blood and release semen’s that would help you enjoy viagra for women price powerful orgasms. This bad stress can also drive people to: drink too much smoke too much eat too much sleep viagra purchase canada too little blow off exercise All of these behaviors can lead to even more scar tissue build-up. There is the component t with the name of the substance Shilajith, in Sanskrit means invincible buy cipla viagra rock, it is supposed to make the male body as solid as rock, and it also has a positive effect on impotence and premature ejaculation on males. So what is the difference between the Chinese percolate and the normal brew we use to have? All the non-herbal ones are derived from leaves of the Camellia sinesis plant. viagra cost

In this How To, I will show you how to copy values from one sheet to another sheet. We need to add a button to the current sheet and add the VB code under the click event for this button. This How To will show the step by step below. I have a sheet with the customer information under the Sheet1 worksheet and I want to copy some information to another worksheet. In order to have Macro or Procedure working on this Excel workbook, you need to save Excel file as Macro-Enabled Worksheet (.xlsm) before process the step below.

Step 1. Create New Sheet

Create a new sheet
Name it as Copy
Enter Customer Name on cell A3, Address on B3, and City on C3

Step 2. Add a Button

Under the Developer Menu, click on Insert icon
Select a Button icon from the Form Controls section
Drag and place the button on Excel sheet on the Copy Sheet

Rename the button to “Copy Customer”
Right-Hand click on “Copy Customer” button
Select Assign Macro…

the Assign Macro window will open
Change new Macro name to GetCustomer
Click New button

A Microsoft Visual Basic for Applications will open as shown below. The VB code will be saved under the Module that can be used on any sheet under this workbook

Step 3. Enter VB code

Enter VB code inside the Sub GetCustomer() procedure

Example VB Code

Result:

Full Code:

Sub GetCustomer()
Dim ws1, ws2 As Worksheet
On Error Resume Next    ‘Optional
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Copy")
       With ws2
           .Range("a4:a6") = ws1.Range("b3:b5").Value
       End With
Set ws1 = Nothing
Set ws2 = Nothing
End Sub

Explanation:

Source sheet = Sheet1
Target sheet = Copy
We want to copy the customer name from cell B3(Nancy Thao) to B5(Michael Vang) of Sheet1 worksheet to cell A4 to A6 of Copy worksheet under the same Workbook.

Extra: Enhance VB Copy on the Second Sheet

For this part, I will show how to use the same sub procedure to copy the values/information from one sheet to multiple sheets. We can use this procedure on many buttons. Following the steps below:

Create a new sheet CustW_DOb
Copy the Copy Customer button on the Copy sheet by using the Rigth-hand click method

Paste the Copy Customer button on the CustW_DOb sheet
Right-Hand Click on the button
Select Assign Macro…
the Assign Macro window will open
Select GetCustomer from the Macro name
Click Edit button

A Microsoft Visual Basic for Applications will open as shown below.

Update the VB code shown below:

Result:

Full Code:

Sub GetCustomer()
Dim ws1, ws2, ws3 As Worksheet
Dim CustDOB As String

On Error Resume Next
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Copy")
Set ws3 = Sheets("custW_DOb")
CustDOB = "b3:c5"   'assign cell B3:C5 to CustDOB
Select Case ActiveSheet.Name
    Case Is = "copy"          'for the current sheet is Copy
        With ws2
            .Range("a4:a6") = ws1.Range("b3:b5").Value
        End With
    Case Is = "CustW_DOb"     'for the current sheet is CustW_DOb
            ws3.Range("a3:b5") = ws1.Range(CustDOB).Value
End Select
Set ws1 = Nothing
Set ws2 = Nothing
Set ws3 = Nothing
End Sub

Explanation:

Source sheet = Sheet1
Target sheet = Copy
Target sheet = CustW_DOb
Set ws1 = Sheets(“Sheet1”)
Set ws2 = Sheets(“Copy”)
Set ws3 = Sheets(“custW_DOb”)
We can use the same Copy Customer button with the same procedure on two different sheets by using the Select Case Statement on the VB code. When we click the Copy Customer button on the Copy sheet(Active Sheet), it will process this block of code:

    Case Is = "copy"          'for the current sheet is Copy
       With ws2
           .Range("a4:a6") = ws1.Range("b3:b5").Value
       End With

When we click the Copy Customer button on the CustW_DOb Sheet(Active Sheet), it will process this block of code:

Case Is = "CustW_DOb" 'for the current sheet is CustW_DOb
ws3.Range("a3:b5") = ws1.Range(CustDOB).Value

Note:

The VBA is case sensitive. Case Is = “CustW_DOb” is not the same as Case Is = “CustW_DOB”. In this example, the name of Worksheet in VB code must match with the name of Excel Sheet.