+ Reply to Thread
Results 1 to 7 of 7

"Pull" data from one closed workbood into active workbook with Command Button

  1. #1
    Registered User
    Join Date
    11-25-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    61

    "Pull" data from one closed workbood into active workbook with Command Button

    Hi all,

    I've been using the following code to bring in individual cell values from one closed workbook to an active one. I would like to modify this is possible to bring in multiple cells at once and also pull them into a different worksheet in the active workbook. Basically, my command button is on Sheet1 but I'd like the data to pull into a cell on Sheet2. Thanks all.

    Private Sub CommandButton1_Click()
    With Range("Q9")
    .Formula = "='C:\Users\[Workbook Name.xlsm]Worksheet Name'! N27"
    .Value = .Value
    End With

  2. #2
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: "Pull" data from one closed workbood into active workbook with Command Button

    Hi klunker,

    Welcome to the forum.

    Try adding the sheet name to your macro as shown below"

    Please Login or Register  to view this content.
    Regards,

    David


    When you reply please make it clear WHO you are responding to by mentioning their name.

    If this has been of assistance, please advise. A little thanks goes a long way.
    - Please click on the *Add Reputation button at the bottom of helpful responses.

    Please mark your thread as SOLVED:
    - Click Thread Tools above your first post, select "Mark your thread as Solved".

  3. #3
    Registered User
    Join Date
    11-25-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    61

    Re: "Pull" data from one closed workbood into active workbook with Command Button

    David A Coop,

    Thanks for the help. Exactly what I needed!

  4. #4
    Registered User
    Join Date
    11-25-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    61

    Re: "Pull" data from one closed workbood into active workbook with Command Button

    David A,

    Could I use this same code to pull multiple cell values into an active sheet? For example .Range("Q9","Q10")

    Private Sub CommandButton1_Click()
    With Sheets("Sheet2").Range("Q9","Q10","Q11")
    .Formula = "='C:\Users\[Workbook Name.xlsm]Worksheet Name'! N27,N28,N29"
    .Value = .Value
    End With

    Does this make sense? Thanks again!

  5. #5
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: "Pull" data from one closed workbood into active workbook with Command Button

    Hi klunker,

    It won't work as you posted it, however, if you take the .Value = .Value out of the With statement, the macro will paste the formula into the workbook, and this will adjust itself for the relative address. Then outside the with statement, select the range and convert it back to values.

    Again, I haven't properly tested this, but it should work!

    Regards,

    David

    Please Login or Register  to view this content.

    When you reply please make it clear WHO you are responding to by mentioning their name.

    If this has been of assistance, please advise. A little thanks goes a long way.
    - Please click on the *Add Reputation button at the bottom of helpful responses.

    Please mark your thread as SOLVED:
    - Click Thread Tools above your first post, select "Mark your thread as Solved".

  6. #6
    Registered User
    Join Date
    11-25-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    61

    Re: "Pull" data from one closed workbood into active workbook with Command Button

    David A,

    Thanks again. Worked like a charm!

  7. #7
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: "Pull" data from one closed workbood into active workbook with Command Button

    You're welcome klunker.

    Please don't forget to close the thread and to click the rep button to show your thanks.


    If this has been of assistance, please advise. A little thanks goes a long way.
    - Please click on the *Add Reputation button at the bottom of helpful responses.

    Please mark your thread as SOLVED:
    - Click Thread Tools above your first post, select "Mark your thread as Solved".

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Adding "Command Button" and "Messages Box" in VBA Macro
    By sanjeevkumarmc in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-09-2014, 07:21 AM
  2. Create a Command Button to "Save As" and "Close" an Excel Workbook
    By thedunna in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-26-2013, 05:38 PM
  3. [SOLVED] How to make a Command button run the "FORM" command to fill/populate a table?
    By jrobin7 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-21-2012, 12:26 PM
  4. "Fit to page" printing two tabs in the same workbook using a command button
    By shekkikim in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-21-2012, 06:05 PM
  5. How to change a Command Button caption from "Enable" to "Disable"?
    By Infinity in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-22-2007, 12:14 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1