copy a sheet from one workbook to another

[lezawang]

Hi
I have 2 workbooks are open. Workbooks(1) and Workbooks(2). The code and the button to run the code are in workbooks(1). The code below does not work after I entered the following

x= 1
y = 1
z =2
m = 1

Sub ws_copy_to_different_wb()
    Dim x As Integer
    Dim y As Integer
    Dim z As Integer
    x = InputBox("workbook #")
    y = InputBox("worksheet #")
    z = InputBox("destination book")
    m = InputBox("after which sheet in destination")
    Workbooks(x).Worksheets(y).Copy after:=Workbooks(z).Worksheets(m)
End Sub

Any idea why? thank you very much.

 

[Fluff]

What happens if you declare m as an integer, like you have the others?

 

[DanteAmor]

Try this

Sub ws_copy_to_different_wb()
    Dim x As Integer, y As Integer
    Dim z As Integer, m As Integer
    x = [COLOR=#0000ff]Application[/COLOR].InputBox("workbook #", , , , , , , [COLOR=#0000ff]1[/COLOR])  '[COLOR=#0000ff]Type number[/COLOR]
    y = Application.InputBox("worksheet #", , , , , , , 1)
    z = Application.InputBox("destination book #", , , , , , , 1)
    m = Application.InputBox("after which sheet in destination #", , , , , , , 1)
    Workbooks(x).Worksheets(y).Copy after:=Workbooks(z).Worksheets(m)
End Sub

But you have to verify how many books you have open.
Review the file names with the following so you can see which number corresponds to each book.

Sub test()
  Dim w As Object, n As Long
  n = 1
  For Each w In Workbooks
    MsgBox w.Name & " Number Book : " & n
    n = n + 1
  Next
End Sub

 

[lezawang]

Thank you all for this great help. Sorry I missed that. I only opened 2 workbooks. m was not declared as int. Thanks again

 

[Fluff]

You're welcome & thanks for the feedback

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.