VBA code to customer use of button

[Kumar Shanmugam]

I have data in sheet 2 dealer wise and product wise. I have created two button Dealer and product in sheet 1.

I want VBA code so that if i click on Dealer button. VBA code should copy dealer wise information in sheet 2 and paste into Sheet 1 right from cell A1. and if i click on Product button VBA should copy product wise information in sheet 2 and paste it into cell A1. Please find attached the sample data.

 

[AlanSidman]

Attach these codes to your buttons

Sub Dealer()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    s1.Range("A1:B" & Rows.Count).End(xlUp).Row.ClearContents
    Dim lr As Long
    lr = s2.Range("A" & Rows.Count).End(xlUp).Row
    s2.Range("A4:B" & lr).Copy s1.Range("A1")
End Sub

Sub Product()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    s1.Range("A1:B" & Rows.Count).End(xlUp).Row.ClearContents
    Dim lr As Long
    lr = s2.Range("E" & Rows.Count).End(xlUp).Row
    s2.Range("E4:F" & lr).Copy s1.Range("A1")
End Sub

 

[Kenneth Hobson]

For that simple example, with little data, this would work. Right click each control and Assign from these stored in a Module.

See this if you need help with Assign for Form controls. https://support.office.com/en-us/ar...l-button-d58edd7d-cb04-4964-bead-9c72c843a283

Sub bDealer()
  Sheet1.UsedRange.Clear
  Sheet2.Range("B4").CurrentRegion.Copy [A1]
End Sub

Sub bProduct()
  Sheet1.UsedRange.Clear
  Sheet2.Range("E4").CurrentRegion.Copy [A1]
End Sub

 

[rahulshewale1]

hii @Kumar Shanmugam ,

Go to developer tab ,insert module copy code and paste and run it.

Sub Dealer_data()
'
' Macro4 Macro
'

'
    Sheets("Sheet2").Select
    Columns("A:B").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
End Sub

Sub Product_data()
'
' Product_data Macro
'
' Keyboard Shortcut: Ctrl+Shift+P
'
    Sheets("Sheet2").Select
    Columns("E:F").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
End Sub

Regard
Rahul shewale