Button to import data from another workbook

[Lelewiwi]

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.

 

[HighAndWilder]

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.

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?

 

[Lelewiwi]

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!

 

[HighAndWilder]

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!

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.

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

 

[Lelewiwi]

@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

 

[Lelewiwi]

@HighAndWilder Any thoughts? Thank you!