none
[Excel 2k3+VBA] Copying entire sheet from workbook to another RRS feed

  • Question

  • Hi,

    I have 3 Excel files, if sheet2 in book1 do not match sheet1 in book2 then copy sheet1 in book2 in sheet1 in book3:

    Private Sub btnUpdate_Click()
        Dim i As Long, n As Long
        n = Application.Max(Workbooks("book1.xls").Sheets("Sheet2").Range("A65536").End(xlUp).Row, Workbooks("book2.xls").Sheets("Sheet1").Range("G65536").End(xlUp).Row)
        For i = 2 To n
            If Workbooks("book1.xls").Sheets("Sheet2").Range("A" & i).Value <> Workbooks("book2.xls").Sheets("Sheet1").Range("G" & i).Value Then
            'Ranges that will be updated if match found
            Else
            'Blind copy of ranges because no match found
                Workbooks("book3.xls").Sheets("Sheet1").Range("A:FS" & i).Value = Workbooks("Copy of Estate Job.xls").Sheets("Sheet1").Range("A:FS" & i).Value
            End If
        Next i
    
    End Sub


    But the compiler stops at

                Workbooks("book3.xls").Sheets("Sheet1").Range("A:FS" & i).Value = Workbooks("Copy of Estate Job.xls").Sheets("Sheet1").Range("A:FS" & i).Value

    How can I fix this, please?

    Thanks

    Friday, February 10, 2012 3:56 PM

Answers

  • Hi,

    I got it to work:

        Workbooks("book1.xls").Sheets("Sheet1").Copy after:=Workbooks("book2.xls").Sheets("Sheet1")
        Application.DisplayAlerts = False
            Workbooks("book2.xls").Sheets("Sheet1").Delete
        Application.DisplayAlerts = True
        Application.Workbooks("book2.xls").Sheets("Sheet1 (2)").Name = "Sheet1"
    Workbooks("Estate Job (updated).xls").Sheets("Sheet1").Select

    Thanks anyway
    • Marked as answer by Admin-Dev Friday, February 10, 2012 5:47 PM
    Friday, February 10, 2012 5:47 PM

All replies

  • "A:FS" & i

    is not a valid range (appearing in two places). Try

    "A1:FS" & i

    Not sure if that is what you mean, so you may need to adjust the range based on your logic.


    HTH, Bernie

    Friday, February 10, 2012 4:32 PM
  • Hi,

    this code takes forever to copy and paste (10000 rows), so I changed it by this one:

    Workbooks("book1").Sheets("Sheet1").Copy after:=Workbooks("book2").Sheets("Sheet1")

    but, the problem is that it do not copy into sheet1, but it creates a sheet1 (2) instead?!

    How to fix this, please?

    Regards

    Friday, February 10, 2012 5:13 PM
  • Hi,

    I got it to work:

        Workbooks("book1.xls").Sheets("Sheet1").Copy after:=Workbooks("book2.xls").Sheets("Sheet1")
        Application.DisplayAlerts = False
            Workbooks("book2.xls").Sheets("Sheet1").Delete
        Application.DisplayAlerts = True
        Application.Workbooks("book2.xls").Sheets("Sheet1 (2)").Name = "Sheet1"
    Workbooks("Estate Job (updated).xls").Sheets("Sheet1").Select

    Thanks anyway
    • Marked as answer by Admin-Dev Friday, February 10, 2012 5:47 PM
    Friday, February 10, 2012 5:47 PM