Run Time 1004 Error

  • Im using the following code

    Set PasteRange = Workbooks("DCTdb.xls").Sheets("YTDdb").Range(Cells(2, Uwk), Cells(71, Uwk))

    When Sheet "YTDdb" is active this works fine, no problems.
    When this sheet is not active, then i get run-time error '1004'

    What am I doing wrong?? Sheet YTDdb is hidden so I cant have it active.


  • Re: Run Time 1004 Error

    Hi MV,

    Your own observation should have given you a clue. ;)

    An unqualified Cells reference will always refer to the ActiveSheet. Hence, when your target sheet is active the code will work as intended. Try explicitly qualifying the Cells references like this:
    [vba]With Workbooks("DCTdb.xls").Sheets("YTDdb")
    Set PasteRange = .Range(.Cells(2, Uwk), .Cells(71, Uwk))
    End With[/vba]HTH

  • Re: Run Time 1004 Error


    could you not do this i.e make the sheet visible whilst the code is running then unhide again? If you turn off screen updating then it still cannot be seen whilst the sode is running:

    Application.ScreenUpdating = False
    Worksheets("YTDdb").Visible = xlSheetVisible
           'your code i.e.set etc etc
    Worksheets("YTDdb").Visible = xlSheetHidden
    Application.ScreenUpdating = True

    Hope that helps

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!