Button to import data from another workbook

L

Lelewiwi

Board Regular
Joined
Nov 8, 2023
Messages
50
Office Version
  1. 365
Platform
  1. Windows
Hello helpful people!

I am trying to import specific cell data from Workbook1 to Workbook2. This needs to be an on-demand action so a button would be best to simplify matters. The button should be on Workbook2 as Workbook1 will change every time. I have never written code nor even looked at anything resembling code so please bear with me and give me simple instructions.

I would like the button to do the following actions:
*Open a window to select Workbook1
*Once Workbook1 is selected, import specific cell data to Workbook2

Data will be a mixture of text, numbers and formulas.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
Lelewiwi said:
Hello helpful people!

I am trying to import specific cell data from Workbook1 to Workbook2. This needs to be an on-demand action so a button would be best to simplify matters. The button should be on Workbook2 as Workbook1 will change every time. I have never written code nor even looked at anything resembling code so please bear with me and give me simple instructions.

I would like the button to do the following actions:
*Open a window to select Workbook1
*Once Workbook1 is selected, import specific cell data to Workbook2

Data will be a mixture of text, numbers and formulas.
Click to expand...
Welcome to MrExcel

There are a few questions:

1. Which worksheet in Workbook1 is the data on?
2. Which worksheet in Workbook2 is the data to go onto?
3. Which range of cells does the data come from on the sheet in Workbook1? e,g, A1:R200
4. Where is the data to be copied to on Workbook2? e.g. A15

Two things that you will need to do:

Are you able to create a button on the sheet in Workbook2?
Are you familiar enough with the VBA Editor to be able to copy and paste the code into a standard code module?
 
Upvote 0
Hi there @HighAndWilder

1. For Workbook1 the data is on multiple sheets, unfortunately.
* Ex: Name is on Sheet1, Score is on Sheet2, etc.
2. Workbook2 is all just on Sheet1.
3 & 4. There is no range, I want specific cells copied over. However, Workbook1, Sheet1's total range is A1:D10 and Workbook1, Sheet2's total range is A1:K44
* Cell A1 from Workbook1, Sheet1 needs to go to cell H7 on Workbook2, Sheet1
* Cell B4 from Workbook1, Sheet2 needs to go to cell AO5 on Workbook2, Sheet1

I have been able to create a button and have the Developer tab activated. I have done a tiny bit of code in Adobe, but this is a whole different beast. Yes I can copy and paste code and if given an example, can change parameters of the code to include more items. (Ex: I have more than 1 cell that I would like copied over, not just the one listed above.)

Thanks so much!
 
Upvote 0
Lelewiwi said:
Hi there @HighAndWilder

1. For Workbook1 the data is on multiple sheets, unfortunately.
* Ex: Name is on Sheet1, Score is on Sheet2, etc.
2. Workbook2 is all just on Sheet1.
3 & 4. There is no range, I want specific cells copied over. However, Workbook1, Sheet1's total range is A1:D10 and Workbook1, Sheet2's total range is A1:K44
* Cell A1 from Workbook1, Sheet1 needs to go to cell H7 on Workbook2, Sheet1
* Cell B4 from Workbook1, Sheet2 needs to go to cell AO5 on Workbook2, Sheet1

I have been able to create a button and have the Developer tab activated. I have done a tiny bit of code in Adobe, but this is a whole different beast. Yes I can copy and paste code and if given an example, can change parameters of the code to include more items. (Ex: I have more than 1 cell that I would like copied over, not just the one listed above.)

Thanks so much!
Click to expand...

Follow instructions 1 to 4 on this page to create a module and copy the code below into it.
Insert and run VBA macros in Excel - step-by-step guide

Note: There is a copy icon in the top right hand corner of the VBA Code box below.

Then when you insert a Button (Form Control) onto the worksheet you can assign this Macro (subImportData) to it.

There are two lines of code to get you started below this maker text.
' LOOK AT THESE TWO LINES OF CODE.

Let me know how you get on.

VBA Code: 
Public Sub subImportData()
Dim fd As Office.FileDialog
Dim strFile As String
Dim WbThisWorkbook As Workbook
Dim WbImportFrom As Workbook

  ActiveWorkbook.Save
  
  Application.ScreenUpdating = False
  
  Set WbThisWorkbook = ThisWorkbook

  Set fd = Application.FileDialog(msoFileDialogFilePicker)
  
  With fd
  
      .Filters.Clear
      .Filters.Add "Excel Files", "*.xlsx?", 1
      .Filters.Add "Excel Files", "*.xlsm?", 1
      .Title = "Choose an Excel file"
      .AllowMultiSelect = False
  
      .InitialFileName = ActiveWorkbook.Path
  
      If .Show = True Then
  
        strFile = .SelectedItems(1)
  
      End If
  
  End With

  If strFile = "" Then
    Application.ScreenUpdating = True
    Exit Sub
  End If
  
  ' Open the source workbook.
  Workbooks.Open strFile, ReadOnly:=True
  
  ' Set an object to this workbook.
  Set WbImportFrom = ActiveWorkbook
  
  ' LOOK AT THESE TWO LINES OF CODE.
  
  ' This line copies the value from range A1 in Sheet1 in the source workbook to
  ' range A1 in the destination workbook.
  WbThisWorkbook.Sheets("Sheet1").Range("A1").Value = WbImportFrom.Sheets("Sheet1").Range("A1").Value
  
  ' You can just copy the above line of code and change it to, for example, take the value from range D16
  ' in the source workbook and copy it to range B13 in the destination workbook.
  WbThisWorkbook.Sheets("Sheet1").Range("B13").Value = WbImportFrom.Sheets("Sheet1").Range("D16").Value
    
  WbImportFrom.Close
  
  Application.ScreenUpdating = True
  
  ActiveWorkbook.Save
  
End Sub
 
Upvote 0
@HighAndWilder

Okay, finally got back to this! I did as you instructed. I was able to choose the file that I wanted to import from but received a "Subscript out of range" message. Below is the code:

Public Sub subImportData()
Dim fd As Office.FileDialog
Dim strFile As String
Dim WbThisWorkbook As Workbook
Dim WbImportFrom As Workbook

ActiveWorkbook.Save

Application.ScreenUpdating = False

Set WbThisWorkbook = ThisWorkbook

Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd

.Filters.Clear
.Filters.Add "Excel Files", "*.xlsx?", 1
.Filters.Add "Excel Files", "*.xlsm?", 1
.Title = "Choose an Excel file"
.AllowMultiSelect = False

.InitialFileName = ActiveWorkbook.Path

If .Show = True Then

strFile = .SelectedItems(1)

End If

End With

If strFile = "" Then
Application.ScreenUpdating = True
Exit Sub
End If

' Open the source workbook.
Workbooks.Open strFile, ReadOnly:=True

' Set an object to this workbook.
Set WbImportFrom = ActiveWorkbook

' LOOK AT THESE TWO LINES OF CODE.

' This line copies the value from range A1 in Sheet1 in the source workbook to
' range A1 in the destination workbook.
WbThisWorkbook.Sheets("Sheet1").Range("B1").Value = WbImportFrom.Sheets("Sheet1").Range("H7").Value

' You can just copy the above line of code and change it to, for example, take the value from range D16
' in the source workbook and copy it to range B13 in the destination workbook.
WbThisWorkbook.Sheets("Sheet1").Range("B2").Value = WbImportFrom.Sheets("Sheet1").Range("R5").Value

' This line copies the value from range A1 in Sheet1 in the source workbook to
' range A1 in the destination workbook.
WbThisWorkbook.Sheets("Sheet2").Range("C27").Value = WbImportFrom.Sheets("Sheet1").Range("E25").Value

' You can just copy the above line of code and change it to, for example, take the value from range D16
' in the source workbook and copy it to range B13 in the destination workbook.
WbThisWorkbook.Sheets("Sheet2").Range("C28").Value = WbImportFrom.Sheets("Sheet1").Range("E26").Value

' This line copies the value from range A1 in Sheet1 in the source workbook to
' range A1 in the destination workbook.
WbThisWorkbook.Sheets("Sheet2").Range("C29").Value = WbImportFrom.Sheets("Sheet1").Range("E27").Value

' You can just copy the above line of code and change it to, for example, take the value from range D16
' in the source workbook and copy it to range B13 in the destination workbook.
WbThisWorkbook.Sheets("Sheet1").Range("B7").Value = WbImportFrom.Sheets("Sheet1").Range("D32").Value

WbImportFrom.Close

Application.ScreenUpdating = True

ActiveWorkbook.Save

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Importing specific cell data from one workbook to another
Replies
1
Views
381
rlv01
R
L
  • Solved
Shift values to the right on demand with button
Replies
3
Views
454
Micron
M
T
  • Solved
Copy and Past the Data from Multiple Closed Workbooks
2
Replies
16
Views
897
thespardian
T
F
  • Question
VBA for copying specific cells from many workbooks into one workbook
Replies
1
Views
472
rlv01
R
D
  • Solved
Copy data from workbook to another workbook
Replies
2
Views
653
Demirion
D

Forum statistics

Threads
1,215,084
Messages
6,123,021
Members
449,092
Latest member
ikke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top