Hi,
I'm very new at VBA, and I want to learn how to import a specific block of data from a closed workbook to my active workbook (in which my macro will be placed) in the following manner:
1. Prompt the user to select the workbook
2. Prompt the user to select the sheet
3. Prompt the user to select the range of columns (Eg: A:E)
4. By default select range of columns C:D as well
4. Import the columns C:D, and the selected range of columns to a particular sheet in my workbook (which is active)
Something like this:
Sub ImportData()
Dim wkbCrntWorkBook As Workbook
Dim wkbSourceBook As Workbook
Dim rngSourceRange, rngSourceRange2 As Range
Dim rngDestination, rngDestination2 As Range
Set wkbCrntWorkBook = ActiveWorkbook
With Application.FileDialog(msoFileDialogOpen)
.Filters.Clear
.Filters.Add "Excel 2002-03", "*.xls", 1
.Filters.Add "Excel 2007", "*.xlsx; *.xlsm; *.xlsa", 2
.AllowMultiSelect = False
.Show
If .SelectedItems.Count > 0 Then
Workbooks.Open .SelectedItems(1)
Set wkbSourceBook = ActiveWorkbook
*'code to select sheet from the workbook*
Set rngSourceRange = Application.InputBox(prompt:="Select source range", Title:="Source Range", Default:="A1", Type:=8)
Set rngSourceRange2 = wkbSourceBook.ActiveSheet.Cells(2, 4) ' required to select column D completely
wkbCrntWorkBook.Activate
Set rngDestination = ActiveSheet.Cells(2, 2) ' paste destination must be column B onwards, i.e, whatever the user selects as range
Set rngDestination2 = ActiveSheet.Cells(1, 1) ' paste destination must be column A, i.e, from D of other sheet to A of this sheet
rngSourceRange.copy rngDestination
rngSourceRange2.copy rngDestination2
rngDestination.CurrentRegion.EntireColumn.AutoFit
wkbSourceBook.Close False
End If
End With
End Sub
Please advise!!
I'm very new at VBA, and I want to learn how to import a specific block of data from a closed workbook to my active workbook (in which my macro will be placed) in the following manner:
1. Prompt the user to select the workbook
2. Prompt the user to select the sheet
3. Prompt the user to select the range of columns (Eg: A:E)
4. By default select range of columns C:D as well
4. Import the columns C:D, and the selected range of columns to a particular sheet in my workbook (which is active)
Something like this:
Sub ImportData()
Dim wkbCrntWorkBook As Workbook
Dim wkbSourceBook As Workbook
Dim rngSourceRange, rngSourceRange2 As Range
Dim rngDestination, rngDestination2 As Range
Set wkbCrntWorkBook = ActiveWorkbook
With Application.FileDialog(msoFileDialogOpen)
.Filters.Clear
.Filters.Add "Excel 2002-03", "*.xls", 1
.Filters.Add "Excel 2007", "*.xlsx; *.xlsm; *.xlsa", 2
.AllowMultiSelect = False
.Show
If .SelectedItems.Count > 0 Then
Workbooks.Open .SelectedItems(1)
Set wkbSourceBook = ActiveWorkbook
*'code to select sheet from the workbook*
Set rngSourceRange = Application.InputBox(prompt:="Select source range", Title:="Source Range", Default:="A1", Type:=8)
Set rngSourceRange2 = wkbSourceBook.ActiveSheet.Cells(2, 4) ' required to select column D completely
wkbCrntWorkBook.Activate
Set rngDestination = ActiveSheet.Cells(2, 2) ' paste destination must be column B onwards, i.e, whatever the user selects as range
Set rngDestination2 = ActiveSheet.Cells(1, 1) ' paste destination must be column A, i.e, from D of other sheet to A of this sheet
rngSourceRange.copy rngDestination
rngSourceRange2.copy rngDestination2
rngDestination.CurrentRegion.EntireColumn.AutoFit
wkbSourceBook.Close False
End If
End With
End Sub
Please advise!!
Last edited: