[Solved] Error 1004 - due to copying?

  • Hi All.
    I am importing some data from an old workbook to a new one. In the new workbook sheet, where the data shall be, I have a commandbottom (only visible this half of the year) with the following code:

    Private Sub OldWeeks_Click()

    Application.ScreenUpdating = False
    Select Case OldWeeks.Caption
    Case "Show all weeks"
    Columns("D:GJ").Hidden = False
    For i = 4 To 192
    If Cells(4, i).Value = "" Or (Cells(2, i).Value = "Su" And Worksheets("Medarbejdere").Cells(50, 3).Value = "No") Then Columns(i).Hidden = True
    Next i
    OldWeeks.Caption = "Hide old weeks"
    Case "Hide old weeks"
    Dato = DateValue("1/" & 6 * Left(Me.Name, 1) - 5 & "/" & Right(Me.Name, 4))
    Dato = Dato - Weekday(Dato)
    i = 4
    Do Until Dato = Date - Weekday(Date) or i>192
    Columns(i).Hidden = True ’’’’’’’’’ Error in this line ’’’’’’’’’’’’’’’’’’’
    Dato = Dato + 1
    i = i + 1
    OldWeeks.Caption = "Show all weeks"
    End Select
    Cells(ActiveCell.Row + 1, ActiveCell.Column + 1).Activate
    Application.ScreenUpdating = True

    End Sub

    When I am copying a lot of data I will get a Run-time error ’1004’ in the columns(i).Hidden =True. (the value of i is around 33 and 80)
    Why do I get this error???

    Help please.


  • Hi Woody

    If using Less than Excel 2000, ensure the TakeFocusOnClick Property is set to False. Or, ass the line ActiveCell.Activate as the first line of code.

    Also, try hiding the same column (value of i) manually. Sometimes Excel will spit out "Cannot hide column", or "Cannot shift objects off sheet", or words to that effect. If it does, and you have cell comments, try this

    Because cell comments are hidden by default, select the cell containing the comment. Right-click the cell, and then click Show Comment on the shortcut menu.

    Move the pointer to the edge of the comment until the pointer turns into a white arrow pointer with four small black arrows on the pointer. Click the comment to select the comment object.

    On the Format menu, click Comment. In the Format Comment dialog box, click the Properties tab.

    Click Move and size with cells, and then click OK.

    If you want to hide the cell comment again, right-click the cell and click Hide Comment on the shortcut menu.

    Take from:

Participate now!

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