Import selected Worksheets from another workbook

[Debrup]

Hi Guys,


I need to copy and paste data from multiple workbooks and multiple sheets. (name keeps changing of worksheets as well as workbook)


I have a code but here I need to manually select the cells. I just want to select the sheet and it should automatically import the entire data in the sheet to a designated worksheet in my workbook. Is that possible?


Sub ImportDatafromotherworksheet()
Dim wkbCrntWorkBook As Workbook
Dim wkbSourceBook As Workbook
Dim rngSourceRange As Range
Dim rngDestination As Range
Set wkbCrntWorkBook = ActiveWorkbook
With Application.FileDialog(msoFileDialogOpen)
.Filters.Clear
.Filters.Add "Excel 2007-13", "*.xlsx; *.xlsm; *.xlsa"
.AllowMultiSelect = False
.Show
If .SelectedItems.Count > 0 Then
Workbooks.Open .SelectedItems(1)
Set wkbSourceBook = ActiveWorkbook
Set rngSourceRange = Application.InputBox(prompt:="Select source range", Title:="Source Range", Default:="A1", Type:=8)
wkbCrntWorkBook.Activate
Set rngDestination = Application.InputBox(prompt:="Select destination cell", Title:="Select Destination", Default:="A1", Type:=8)
rngSourceRange.Copy rngDestination
rngDestination.CurrentRegion.EntireColumn.AutoFit
wkbSourceBook.Close False
End If
End With
End Sub

 

[Joe4]

Welcome to the Board!

You can select all the cells of the ActiveSheet with this command:

Cells.Select

Or, to copy them, it would just be:

Cells.Copy

Then just go to cell A1 of your destination sheet and do:

Activesheet.Paste

Or, you can use the method shown in the first response here:
https://www.mrexcel.com/forum/excel-questions/1044124-copying-sheet-one-workbook-another-vba.html
(there was some back and forth that it wasn't working for the user, but that appears to be because they have merged cells - and merged cells are very bad!!!).