Copy Action Failed Excel VBA

Posted on 2012-08-31
Last Modified: 2012-08-31
Hello ,

This is reference to this question before:
Runtime Error 1004
Method Range of Object Worksheet Failed
This is happening at line:

Sheet1.Range(Cells(7, xCol), Cells(xLast_Row, xCol)).Copy

It is expected that sheet1 will be hidden. The button will be on sheet2 (“Main-Front”)
What can be done to that the error is removed…..
Question by:Rayne
    LVL 33

    Accepted Solution

    The problem is that there's no sheet reference for Cells.

    This will mean that VBA will refer to Cells on the active sheet, which is probably not Sheet1.

    So you are sort of trying to make a range on Sheet1 with cells from another sheet.

    Try this.
    Option Explicit
    Sub Bulk_Update()
    Dim xLast_Row As Long
    Dim xCol As Long
    Dim xRange As Range
        xLast_Row = 700
        If xLast_Row < 7 Then
            MsgBox ("No Data rows. Run terminated.")
            Exit Sub
        End If
        If Sheet1.[H2] = "" Then
            MsgBox ("""HEapTotal"" is invalid. Run terminated.")
            Exit Sub
        End If
        If Sheet1.[H3] <> "add on1" And [H3] <> "add on2" Then
            MsgBox ("""EffectedColumn"" is invalid. Run terminated.")
            Exit Sub
        End If
        If Sheet1.[H4] = "" Then
            MsgBox ("""Date"" is invalid. Run terminated.")
            Exit Sub
        End If
        Application.ScreenUpdating = False
        ' Remeber the active cell...
        Set xRange = ActiveCell
        ' Which column are we updating?
        If Sheet1.[H3] = "add on1" Then xCol = 17 Else xCol = 18
        ' Flag rows for update...
        With Sheet1
            .Cells(7, xCol).Formula = "=IF(AND(F7=$H$4,OR(B7<>"""",C7<>"""",D7<>"""",E7<>"""")),1,"""")"
            .Cells(7, xCol).Copy Destination:=.Range(.Cells(7, xCol), .Cells(xLast_Row, xCol))
        End With
        ' Calculate amount to update each row...
        Sheet1.Cells(5, xCol).FormulaR1C1 = "=R2C8/SUM(R[2]C:R[" & xLast_Row & "]C)"
        ' Drop formulas in update column...
        'Sheet1.Range(Cells(7, xCol), Cells(xLast_Row, xCol)).Select
        With Sheet1
            .Range(.Cells(7, xCol), .Cells(xLast_Row, xCol)).Copy
            .Range(.Cells(7, xCol), .Cells(xLast_Row, xCol)).PasteSpecial xlPasteValues
            ' Replace flag by update amount...
            .Range(.Cells(7, xCol), .Cells(xLast_Row, xCol)).Replace What:="1", Replacement:=.Cells(5, xCol), LookAt:=xlWhole
            ' Clear work cell...
            .Cells(5, xCol).ClearContents
        End With
        Application.ScreenUpdating = True
        ' Finished...
        MsgBox ("Update complete.")
    End Sub

    Open in new window

    I'm assuming Cells(5, xCol) is on Sheet1.
    LVL 10

    Expert Comment

    Did you mean this:
    Sheet1.Range(Sheet1.Cells(7, xCol), Sheet1.Cells(xLast_Row, xCol)).Copy

    Author Comment

    Thank you Imnorie

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
    My experience with Windows 10 over a one year period and suggestions for smooth operation
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now