Sunday, October 11, 2015

Excel copy from closed file with different column mapping

Private Sub CommandButton1_Click()

Dim wb1 As Workbook
Dim ws1 As Worksheet

Dim wbCopy As Worksheet

Set wbCopy = ActiveSheet

Pathname = ActiveWorkbook.Path & "\Files\"
Filename = Dir(Pathname & "*.xlsm")
Set wb1 = Workbooks.Open(Pathname & Filename)


With wb1.Sheets(1)
 .Range("B4:H4").Select
.Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
End With

wbCopy.Activate
    Range("B4").Select
    ActiveSheet.Paste
    Range("I4").Select
wb1.Activate
With wb1.Sheets(1)
    .Range("K4").Select
    .Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
End With
wbCopy.Activate
    Range("I4").Select
    ActiveSheet.Paste
wb1.Close



End Sub

No comments:

Post a Comment