Hi,
I am trying to import a file that allows a user to select which tab and range to bring in from the import file. On my "Sheet Mapping" tab, C3 = tab I want to copy, D3 = Range I want to copy, B3=tab I want to copy to, and E3 = range I want to copy to. See picture attached. The data in the file I am importing varies in size, so I want the user to be able to define the range in order to minimize the data that would be required for a one-size-fits-all range. The code below is not working. Any help is appreciated.
Sub Importme()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Sheets("Sheet Mapping").Select
With Application.FileDialog(msoFileDialogOpen)
.InitialFileName = ActiveWorkbook.Path
.Title = "Please select import workbook"
.AllowMultiSelect = False
If .Show = -1 Then Range("b1").Value = .SelectedItems(1)
'''''Define Object for Target Workbook
Dim Target_Workbook As Workbook
Dim Source_Workbook As Workbook
Dim Target_Path As String
Dim Target_Tab As Worksheet
Dim Target_Range As Range
Dim Source_Tab As Worksheet
Dim Source_Range As Range
'''''Assign the Workbook File Name along with its Path
'''''Change path of the Target File name
Target_Path = Range("b1").Value
Set Target_Workbook = Workbooks.Open(Target_Path)
Set Source_Workbook = ThisWorkbook
Set Target_Tab = Range("c5").Worksheet
Set Source_Tab = Range("b5").Worksheet
Set Target_Range = Range("D5").Value
Set Source_Range = Range("e5").Value
Set Target_Data = Target_Workbook.Sheets(Source_Tab).Range(Source_Range)
Source_Workbook.Sheets(Target_Tab).Range(Target_Range) = Target_Data
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
'''''Process Completed
MsgBox "File imported"
End With
End Sub
