Run time error 1004: says sheet protected when it is not

[alveeru]

Hi All i have two issue in below code.

1. its give me Run time error 1004: cannot proceed as sheet is protected, when it is not protected.
2. i want it to run from 3 sheet to run till last sheet,,,but it is keep running on 1 sheet only.

Sub MacroTest()


Dim i As Integer


    For i = 3 To Worksheets.Count
    Application.DisplayAlerts = False
    Sheets(i).Activate
'    Sheets(i).Unprotect "conso"
    Columns("A:A").EntireColumn.Select
    Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="*", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
        DecimalSeparator:=",", ThousandsSeparator:=".", TrailingMinusNumbers:= _
        True
    ActiveCell.Range("A1").Select
    Cells.Replace What:="=-", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    ActiveCell.Columns("E:I").EntireColumn.Select
    ActiveCell.Columns("E:I").EntireColumn.EntireColumn.AutoFit
    Application.DisplayAlerts = True


    Next i






End Sub

please help!!

 

[Norie]

Does this work any better?

Sub MacroTest()
Dim I As Long

    Application.DisplayAlerts = False

    For I = 3 To Worksheets.Count
        With Sheets(I)
            .Unprotect "conso"
            With .Columns("A:A").EntireColumn
                .TextToColumns Destination:=.Cells(1, 1), DataType:=xlDelimited, _
                    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
                    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
                    :="*", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
                    DecimalSeparator:=",", ThousandsSeparator:=".", TrailingMinusNumbers:= _
                    True
            End With
        
            .Cells.Replace What:="=-", Replacement:="", LookAt:=xlPart, SearchOrder _
            :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        
            .Columns("E:I").EntireColumn.EntireColumn.AutoFit
            
        End With
    
    Next I
    
    Application.DisplayAlerts = True
    
End Sub

 

[alveeru]

Hi Noori, thank you for your time and help...but i am still facing same issue...

"Run time error 1004: you can not use this command on a protected sheet...etc..."

but my excel file or sheet is not in protected mode.

please help!

 

[Norie]

Can you post the code you tried and indicate where you get the error?

PS Where is the code located?

 

[alveeru]

Can you post the code you tried and indicate where you get the error?

PS Where is the code located?

here is code:

Sub MacroTest()
Dim I As Long


Application.DisplayAlerts = False


For I = 3 To Worksheets.Count
With Sheets(I)
' .Unprotect "conso"
With .Columns("A:A").EntireColumn
.TextToColumns Destination:=.Cells(1, 1), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="*", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
DecimalSeparator:=",", ThousandsSeparator:=".", TrailingMinusNumbers:= _
True
End With

.Cells.Replace What:="=-", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

.Columns("E:I").EntireColumn.EntireColumn.AutoFit

End With

Next I

Application.DisplayAlerts = True

End Sub


i am getting stuck at red part.....

 

[Norie]

Why is this commented out?

'.Unprotect "conso"