moving a sheet to different workbook - vba

[lezawang]

Hi I wrote the code below. It did not give an error message but it did not move a sheet from from one workbook to another. The main workbook is called Book1 and the file I want to open is 1.xlsx. I passed the path and file name correctly. The second file will open but the copy would happen. Thank you very much

' moving a sheet to different workbook
Sub movingtofile()
    Application.DisplayAlerts = False
    Dim x As Integer
    Dim xfile As Workbook
    Dim mfile As String
    mfile = InputBox("full file path")
    Set xfile = Workbooks.Open(mfile)
    x = InputBox("which sheet you want to copy")
    Workbooks("book1").Worksheets(x).Copy after:=xfile.Worksheets(1)
    xfile.Save
    xfile.Close
    Application.DisplayAlerts = False
End Sub

 

[Norie]

Does this work?

' moving a sheet to different workbook
Sub movingtofile()
Dim x As Integer
Dim xfile As Workbook
Dim mfile As String

    mfile = InputBox("full file path")

    Set xfile = Workbooks.Open(mfile)

    x = InputBox("which sheet you want to copy")

    Workbooks("book1").Worksheets(x).Copy after:=xfile.Worksheets(1)

    xfile.Close SaveChanges:=True

End Sub