VBA Sheet Mapping

ndbeck

New Member
Joined
Jan 9, 2014
Messages
6
Capture.JPG


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
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Watch MrExcel Video

Forum statistics

Threads
1,122,168
Messages
5,594,632
Members
413,919
Latest member
ZaxAlchemist

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
Top