I am attempting to import data out of a weekly excel sheet, into the master workbook. The goal is to have 3 things ha[[en when the user clicks the button:
1 - MsgBox pops up saying "Be sure to select the correct destination worksheet before clicking OK."
1.2 - Allow user to select correct sheet if they have not, then click OK. If this cannot be done, then is it possible to place a cancel option in the box, so that they can select the correct sheet, then try again?
2 - Import the data from the file selected into the active worksheet.
3- Force a sort so that the format of the sheet matches the rest of the master workbook.
Here is the code that I have so far.
Private Sub BTNimportdata_Click()
MsgBox "Be sure to select the correct destination worksheet before clicking OK."
Dim customerBook As Workbook
Dim filter As String
Dim caption As String
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook
Set targetWorkbook = Application.ActiveWorkbook
filter = "Excel Files (*.xl*;*.xlsx;*.xlsm;*.xlsb;*.xlam;*.xltx;*.xltm;*.xlz;*.xla;*.xlt;*.xlm;*.xlw),*.xl*;*.xlsx;*.xlsm;*.xlsb;*.xlam;*.xltx;*.xltm;*.xlz;*.xla;*.xlt;*.xlm;*.xlw"
caption = "Please Select an input file "
customerFilename = Application.GetOpenFilename(filter, , caption)
Application.ScreenUpdating = False
Set customerWorkbook = Application.Workbooks.Open(customerFilename)
Dim targetSheet As Worksheet
Set targetSheet = targetWorkbook.Worksheets.ActiveSheet
Dim sourceSheet As Worksheet
Set sourceSheet = customerWorkbook.Worksheets(1)
LRs = sourceSheet.Cells(Rows.Count, "A").End(xlUp).Row
LRt = targetSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
If LRt = 2 Then LRt = 1
sourceSheet.Range("A1:BZ" & LRs).Copy targetSheet.Range("A" & LRt)
customerWorkbook.Close
Dim objSort As Sort
Dim Rng As Range
With ActiveSheet
Set Rng = .UsedRange
Set objSort = .Sort
End With
With objSort
.SortFields.Clear
.SortFields.Add Key:=Columns("I"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortTextAsNumbers
.SortFields.Add Key:=Columns("C"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortTestAsNumbers
.SortFields.Add Key:=Columns("D"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortTextAsNumbers
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SetRange Rng
.SortMethod = xlPinYin
.Apply
End With
Application.ScreenUpdating = True
End Sub
Any help with this would be greatly appreciated.
Thanks,
DaiyannaGrae
Bookmarks