Copy the last sheet from file to another with ignore empty whole column

[MKLAQ]

Hello
I have two files in the same folder"C:\Users\MKK\Desktop\MACROS\"
the closed file contains many sheets and add new sheets every time . every sheet contains many columns . what I want importing the last sheet from closed file to open file with just copy specific columns A,B, and the last column put in column C . the last column in last sheet for closed file will change in location so the last column will be dynamic .
the last column when brings from last sheet in closed file shouldn't be empty . I mean will be some columns are empty after headers in row1 should ignore it , just brings the last column contain numeric values under header even contains some blank cells for the same column contains numbers.
I don't want to keep closed file open when import to open file .
every time run the macro in open file should delete the last imported sheet before importing to replace old data with new data .
thanks .

 

[mumps]

What is the full name of the closed file including extension?

 

[MKLAQ]

both files are xlsm of extension
the closed file's name is STOCK

 

[mumps]

Place this macro in the open file. Change the destination sheet name (in red) to suit your needs.

Sub CopyData()
    Application.ScreenUpdating = False
    Dim desWS As Worksheet, srcWS As Worksheet, lRow As Long, lCol As Long
    Set desWS = ThisWorkbook.Sheets("Sheet1")
    desWS.UsedRange.Delete
    Workbooks.Open "C:\Users\MKK\Desktop\MACROS\STOCK.xlsm"
    Set srcWS = Sheets(Sheets.Count)
    With srcWS
        lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        lCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
        .Range("A1", .Range("B" & .Rows.Count).End(xlUp)).Copy desWS.Range("A1")
        .Range(.Cells(1, lCol), .Cells(lRow, lCol)).Copy desWS.Range("C1")
    End With
    ActiveWorkbook.Close False
    Application.ScreenUpdating = True
End Sub

 

[MKLAQ]

Excellent !
I need for help about this line

    desWS.UsedRange.Delete

actually I add columns after C in open file , so just delete data from column A to C when importing data , should not delete after column C .
thanks again

 

[mumps]

Replace that line of code with this line:

desWS.Range("A1", desWS.Range("C” & Rows.Count).End(xlUp)).ClearContents

 

[MKLAQ]

thank you so much for your assistance .

 

[mumps]

You are very welcome.