Using a button to pull data from another worksheet

**btrfly701** · 01-27-2012, 10:48 AM

Hi, I am brand new with VB, so I am completely lost with this.

My workbook contains a report, that consists of two pages: a Data sheet and a Pulse sheet. I am trying to add a button that, when clicked, fetches information from the Pulse sheet and plugs it into the data sheet. The trick is that the data is pulled and copied to multiple lines and columns. I found this code on a forum, so decided to try it:

Sub Button1_Click()
'
' Button1_Click Macro
' Pulls data from other worksheet
'
' Keyboard Shortcut: Ctrl+d
'
    Selection.OnAction = "Button1_Click"    
Application.Goto Reference:="Button1_Click"
Worksheets("Data").Range("E6").Value = Worksheets("Pulse - Dec-11").Range("N7")
Worksheets("Data").Range("E7").Value = Worksheets("Pulse - Dec-11").Range("N8")
Worksheets("Data").Range("E8").Value = Worksheets("Pulse - Dec-11").Range("N9")
Worksheets("Data").Range("E9").Value = Worksheets("Pulse - Dec-11").Range("N10")
Worksheets("Data").Range("E10").Value = Worksheets("Pulse - Dec-11").Range("N11")
Worksheets("Data").Range("E11").Value = Worksheets("Pulse - Dec-11").Range("N12")

    With Selection.Characters(Start:=1, Length:=8).Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Range("B1").Select
    ActiveSheet.Shapes("Button 1").Select
    Application.Goto Reference:="Button1_Click"
End Sub

When I hit the button, it gives me a run-time error '438' - Object doesn't support this property or method and it highlights the red text above (Selection.OnAction = "Button1_Click")

Any help? I can give more info, just let me know what you need. Thank you!!

**StephenR** · 01-27-2012, 11:58 AM

Can you post a workbook?

**btrfly701** · 01-27-2012, 12:34 PM

Unfortunately I cannot, due to confidential material.

But - good news - I just deleted the line I was having problems with, and it seems to be working fine now! This is what I have now (i changed the names of the sheets to current month and pulse - current month):

Sub Button1_Click()
'
' Button1_Click Macro
' Pulls data from other worksheet
'
' Keyboard Shortcut: Ctrl+d
'
  
Worksheets("Current Month").Range("E6").Value = Worksheets("Pulse - Current Month").Range("N7")
Worksheets("Current Month").Range("E7").Value = Worksheets("Pulse - Current Month").Range("N8")
Worksheets("Current Month").Range("E8").Value = Worksheets("Pulse - Current Month").Range("N9")
Worksheets("Current Month").Range("E9").Value = Worksheets("Pulse - Current Month").Range("N10")
Worksheets("Current Month").Range("E10").Value = Worksheets("Pulse - Current Month").Range("N11")
Worksheets("Current Month").Range("E11").Value = Worksheets("Pulse - Current Month").Range("N12")
Worksheets("Current Month").Range("E12").Value = Worksheets("Pulse - Current Month").Range("N13")
Worksheets("Current Month").Range("E14").Value = Worksheets("Pulse - Current Month").Range("N15")


    
    With Selection.Characters(Start:=1, Length:=8).Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        
    End With
 
End Sub

Thank you for your offer to help, but I think it is good now. If you see any issues that I'm missing, feel free to advise.