?
Solved

Copy Action Failed Excel VBA

Posted on 2012-08-31
3
Medium Priority
?
544 Views
Last Modified: 2012-08-31
Hello ,

This is reference to this question before:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Q_27847240.html 
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…..
bulkStuff-V10.xlsm
0
Comment
Question by:Rayne
3 Comments
 
LVL 35

Accepted Solution

by:
Norie earned 2000 total points
ID: 38356189
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...
    'xRange.Activate
    MsgBox ("Update complete.")

End Sub

Open in new window

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

Expert Comment

by:tdlewis
ID: 38356195
Did you mean this:
Sheet1.Range(Sheet1.Cells(7, xCol), Sheet1.Cells(xLast_Row, xCol)).Copy
0
 

Author Comment

by:Rayne
ID: 38356510
Thank you Imnorie
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

807 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