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
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