copying a range to different file

[lezawang]

Hi
I want to copy a range to another file. I wrote the code below. I can copy a range to same sheet, different sheets but not to different file. The code below did not give me an error message but it wont copy anything. I wrote similar code to copy one cell only and it did work. What is wrong with the code below. I appreciate your help. Thanks.

'working with range
    Sub myrange()
    Application.DisplayAlerts = False
    Dim x As Integer, y As Integer, i As Integer, j As Integer, k As Integer
    x = InputBox("row1")
    y = InputBox("col1")
    i = InputBox("row2")
    j = InputBox("col2")
    k = InputBox("color")
    Range(Cells(x, y), Cells(i, j)).Value = 100
    Range(Cells(x, y), Cells(i, j)).Interior.ColorIndex = k
    'copy range to same sheet
    Range(Cells(x, y), Cells(i, j)).Copy Cells(10, 10)
    'copy to different sheet
    Range(Cells(x, y), Cells(i, j)).Copy Sheets(2).Cells(10, 10)
    'copy to different file
    Dim wb As Workbook
    Dim fn As String
    fn = InputBox("file name")
    Set wb = Workbooks.Open(fn)
    Range(Cells(x, y), Cells(i, j)).Copy wb.Sheets(1).Cells(5, 5)
    wb.Save
    wb.Close
    Application.DisplayAlerts = True
End Sub

 

[Fluff]

When you open another workbook that will be the active workbook.
As you haven't qualified the range to copy, it will copy from the active sheet in the workbook you just opened.

 

[lezawang]

Thank you. I see. I changed that but now i am getting "application define or object define error"!

'copy to different file
    Dim wb As Workbook
    Dim fn As String
    fn = InputBox("file name")
    Set wb = Workbooks.Open(fn)
    ThisWorkbook.Worksheets(1).Range(Cells(x, y), Cells(i, j)).Copy wb.Worksheets(1).Cells(5, 5)
    wb.Save
    wb.Close
    Application.DisplayAlerts = True
End Sub

 

[Fluff]

You need to qualify the cells as well.

 

[lezawang]

Thank you that resolved the issue. I never thought about it. Just another question please. I have Form Control Button in the first file. After trying to run that code several times, somehow I see the Control button in the sheet 1 of the second file! I thought Form Control buttons are not part of cells. How it gets copied to the second file! Thank you.