Friday, September 5, 2025

CopyFromSelectedWorkbookToThisWorkbook

 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