Import data from more than one spreadsheet

Cuzzaa

Board Regular
Joined
Apr 30, 2019
Messages
86
Hi all

Really hoping a genius can help me with a little task... I am using the code below to import data from a separate workbook into my existing workbook. What I'm trying to achieve is for one vba macro button to present the user with 3 separate spreadsheet imports.

I.e:

The user clicks the button and is presented with a 'select file' popup, the user then selects the file and the cell range A1:E100 is then imported into the existing workbook in cells A1:E100 but specifically in the worksheet called 'First'.

Once this has been imported, the next popup comes on the screen to select the second spreadsheet to import the same cell range into the cell range in the existing workbook but into the next worksheet called 'Second'.

Lastly, the user is then presented the popup to select a spreadsheet to import for a third time, consisting of the same cell ranges but to be imported into the existing workbook in worksheet called 'Third'.

Would someone be able to help please? The code below works fine but only if i'm importing one spreadsheet only.

Many thanks in advance for anyone willing to try and help me :)

Code:
Sub Import()

' Get customer workbook...
Dim customerBook As Workbook
Dim filter As String
Dim caption As String
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook


Dim aCount As Integer, msg As String
Const msg1 = "The sheets have been imported!" & vbCr & vbCr






' make weak assumption that active workbook is the target
Set targetWorkbook = Application.ActiveWorkbook




' get the customer workbook
filter = "Text files (*.xls),*.xls"
caption = "Please select the timesheets "
customerFilename = Application.GetOpenFilename(filter, , caption)




Set customerWorkbook = Application.Workbooks.Open(customerFilename)




' assume range is A2 - K200 in sheet1
' copy data from customer to target workbook
Dim targetSheet As Worksheet
Set targetSheet = targetWorkbook.Worksheets(1)
Dim sourceSheet As Worksheet
Set sourceSheet = customerWorkbook.Worksheets(1)




targetSheet.Range("A12", "K208").Value = sourceSheet.Range("A2", "K200").Value




' Close customer workbook
customerWorkbook.Close




' targetSheet.Range("M3").Value = customerFilename


Range("A1").Select
End Sub
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

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