Sub CopyFromSelectedWorkbookToThisWorkbook()
Dim sourceWB As Workbook
Dim targetWB As Workbook
Dim sourceWS As Worksheet
Dim targetWS As Worksheet
Dim copyRange As Range
Dim lastRow As Long, lastCol As Long
Dim filePath As Variant
' Set reference to the target workbook and Sheet1
Set targetWB = ThisWorkbook
'Set targetWS = targetWB.ActiveSheet
Set targetWS = targetWB.Sheets("Sheet1")
targetWS.cells.clear
' Prompt user to select the source workbook (.xls or .xlsx)
filePath = Application.GetOpenFilename("Excel Files (*.xls; *.xlsx), *.xls; *.xlsx", , "Select Source Workbook")
' Exit if user cancels
If filePath = False Then
MsgBox "No file selected.", vbExclamation
Exit Sub
End If
' Open the source workbook
Set sourceWB = Workbooks.Open(filePath)
Set sourceWS = sourceWB.ActiveSheet
' Find the last used row and column in source sheet
lastRow = sourceWS.Cells(sourceWS.Rows.Count, 2).End(xlUp).Row
lastCol = sourceWS.Cells(lastRow, sourceWS.Columns.Count).End(xlToLeft).Column
' Define the range to copy
Set copyRange = sourceWS.Range(sourceWS.Cells(1, 1), sourceWS.Cells(lastRow, lastCol))
' Paste the copied range into Sheet1
copyRange.Copy
targetWS.Range("A1").PasteSpecial Paste:=xlPasteAll
' Optional: Close the source workbook without saving
sourceWB.Close SaveChanges:=False
' Clear clipboard
Application.CutCopyMode = False
End Sub
