Run Time 1004 Error

  • Im using the following code

    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.


    Thanks,
    MVillarr

  • 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


    Hi


    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:

    Code
    Application.ScreenUpdating = False
    Worksheets("YTDdb").Visible = xlSheetVisible
    Worksheets("YTDdb").Activate
           '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!