cut cells to different file

[lezawang]

Hi
I am trying to cut and paste a range. The code below is giving me out of range error mesage. it opens the second file but wont do the cut. Thank you so much

Sub cutcells()
    Dim x As String
    x = InputBox("enter workbooks name")
    Workbooks.Open Filename:=x
    Workbooks(1).Worksheets(1).Range("a1:a25").Cut Workbooks(x).Worksheets(1).Range("e1")
    Workbooks(x).Save
    Workbooks(x).Close
End Sub

 

[JLGWhiz]

If it won't do the cut, maybe Workbooks(1) is not the workbook you think it is. Do you have any other open workbooks? You can step through the code using the F8 key in the vbEditor mode to check variable values. You can insert message boxes to see what workbook and sheet names are for the indexed values.

 

[lezawang]

Thank you. I actually did before posting
?workbooks(1).name
?workbooks(2).name
in the intermediate windows and everything seems fine but still not able to cut

 

[JLGWhiz]

How about protection? On or Off?

 

[JoeMo]

You may be encountering one or more of these error sources:
1. If you have a PERSONAL.XLSB workbook Excel will open it first, but hidden. That makes it the workbook with index 1 even though its hidden. So, referring to open workbooks by their index values can be problematic.
2. If the user enters a full path for x rather than just the name of the workbook for x, then Workbooks(x) will generate a subscript out of range runtime error.
See if this works for you:

Sub cutcells()
    Dim WbSource As Workbook, WbDestination As Workbook, x As String
    Set WbSource = ThisWorkbook
    x = InputBox("enter workbooks name")
    Workbooks.Open Filename:=x
    Set WbDestination = ActiveWorkbook
    WbSource.Worksheets(1).Range("a1:a25").Cut WbDestination.Worksheets(1).Range("e1")
    With WbDestination
        .Save
        .Close
    End With
End Sub

 

[lezawang]

Thank you all, it is working now. Both correct, combination of opening many files, protecting etc..

 

[JLGWhiz]

Happy to help,
Regards, JLG