copy sheet from one wb to another

[lezawang]

Hi
The code below opens the other workbook but does not copy the sheet. I got run-time error 9 message. Thank you so much.

Sub sheet_copy_diff_wb()
    'to copy sheet to different wb
    Dim x As Integer
    Dim s As String
    x = InputBox("enter sheet# you want to copy")
    s = InputBox("enter workbook name you want to copy the sheet to")
    Workbooks.Open (s)
    Workbooks(1).Worksheets(x).Copy after:=Workbooks(s).Worksheets(1)
    Workbooks(s).Save
    Workbooks(s).Close
End Sub

Update: I found the answer. I was entering the second workbook name without extension. for example I was entering Book2
but when I entered Book2.xlsx, everything was ok. My question now when I can enter Workbook name without extension and when with extension because I remember sometime I enter the workbook name without extension and it did work? I do not recall which situation. Thank you again.

 

[My Aswer Is This]

If the Workbook has been saved then you must use full name including extension.

If the Workbook has not been saved then you can use something like this:

Sub Workbook()
'Modified  5/1/2019  8:42:55 PM  EDT
Workbooks("Book2").Sheets(1).Cells(1, 1).Value = "You"
End Sub

 

[Fluff]

If your folder settings are set to "hide known file extensions" then you don't need to include the extension with the file name.
That said it's always best to include the extension.

 

[lezawang]

Thank you very much guys. I was not clear about that. Thanks

 

[Fluff]

You're welcome & thanks for the feedback