External dada copy errors'

[Ks1102]

Hi, please help to see wahat's the code as below which wrong as color RED, the intents is want to get data base on the acitive sheet to paste base on Rrows count from cloumn "B"

Sub testing()
Dim home As Worksheet
Dim Filename As String
Dim Wb As Workbook

With Application.FileDialog(msoFileDialogOpen)

.AllowMultiSelect = False
If .Show = -1 Then

Filename = .SelectedItems(1)
Set home = ActiveWorkbook.ActiveSheet
Range("B1").Select
Set Wb = Workbooks.Open(Filename)
Wb.Worksheets("MAN_SUM").Range("K6:K7").Copy
home("Sheet1").Cells(Rows.Count, "B").End(x1up).Offset(1).Paste Wb.Close (False)
End If
End With
End Sub



your help I highly appreciate

 

[jindon]

should be

home.Cells(Rows.Count, "B").End(xlup).Offset(1).PasteSpecial
Wb.Close (False)

However, you don't need to open

Sub test()
Dim home As Worksheet
Dim Filename As String, myDir As String, fn As String
Set home = ThisWorkbook.ActiveSheet
With Application.FileDialog(msoFileDialogOpen)
    .AllowMultiSelect = False
    If .Show = -1 Then
        Filename = .SelectedItems(1)
        myDir = Left$(Filename, InStrRev(Filename, "\"))
        fn = Mid$(Filename, InStrRev(Filename,"\")+1)
        With home.Cells(Rows.Count, "b").End(xlUp)(2).Resize(2)
            .Formula = "='" & myDir & "[" & fn & "]MAN_SUM'!k6"
            .Value = .Value
        End With
    End If
End With
End Sub

 

[Ks1102]

Dear Jindon

Thanks very much and I happy to learn a many form this forum

Thanks