Run-time Error 1004

[impr]

hiI have a error 1004 when i run the macro
and when i click Debug it shows me that it gets stuck at the Red Text in the code below


I would love to upload the excel sheet, but it is personal info of my client so i can’t


I am using Excel 2011 for Mac
would any one be able to advise how i can fix this?
Below is the code
Thank you in advance!

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


Sub Never()
'
' Never Macro
'

'
Columns("C:C").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("C2").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1], LEN(RC[-1])-2)"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C1054")
Range("C2:C1054").Select
Range("B1").Select
Selection.Copy
Range("C1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Columns("C:C").Select
Selection.Copy
Columns("B:B").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C16").Select
Application.CutCopyMode = False
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Columns("D:F").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("D2").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1], 2)"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D1054")
Range("D2:D1054").Select
Columns("D:D").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("F15").Select
Application.CutCopyMode = False
Columns("D:D").Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="AB", Replacement:="AQ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="W1", Replacement:="W", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="B3", Replacement:="BN", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="CB", Replacement:="BL", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="B1", Replacement:="BLK", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="B4", Replacement:="NB", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="P1", Replacement:="P", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="P2", Replacement:="PU", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="B2", Replacement:="C", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="C1", Replacement:="CHO", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("E2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-3],""-"",RC[-1])"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E1054")
Range("E2:E1054").Select
Range("C1").Select
Selection.Copy
Range("E1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Columns("E:E").Select
Selection.Copy
Columns("C:C").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("E11").Select
Application.CutCopyMode = False
Columns("D:F").Select
Selection.Delete Shift:=xlToLeft
End Sub

[Kenneth Hobs]

Welcome to the forum! Please paste code between code tags. To insert code tags, click the # icon on the toolbar.

That should work. I even tried locking cells and protecting them and still could not duplicate your problem. Most of the time the line of error is marked but on rare occasions it may be off a line. As such, you might look into the protection issued with respect to your PasteSpecial(). You can Protect a worksheet with a password and use the InterfaceOnly:=True option so that code can write to locked cells. I normally set that in ThisWorkbook object's Open event.

I would recommend avoiding Select or Selection. See the link at the end to go beyond the macro recorder.
e.g.



Sub ken()
With Columns("D:D")
.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With
End Subhttp://www.tushar-mehta.com/excel/vba/beyond_the_macro_recorder/

[impr]

Hello
Thank you for your reply
I tried to clear the contents, and i still had the same error at the same code, so now i am uploading the sheet with the clear contents, and i hope that now i can a answer that will help me


Thank you for your help in advance

[SamT]

I hate cleaning OP Macros

This runs with out error on my Excel. Don't know if it does what it is supposed to.


Sub Never()
'
' Never Macro
'

'
Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Range("C2").FormulaR1C1 = "=RIGHT(RC[-1], LEN(RC[-1])-2)"
Range("C2").AutoFill Destination:=Range("C2:C1054")

Range("B1").Copy Range("C1")
Application.CutCopyMode = False

Columns("C:C").Copy
Columns("B:B").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Columns("C:C").Delete

Columns("D:F").Insert CopyOrigin:=xlFormatFromLeftOrAbove

Range("D2").FormulaR1C1 = "=RIGHT(RC[-1], 2)"
Range("D2").AutoFill Destination:=Range("D2:D1054")

Columns("D:D").Copy
Columns("D:D").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Columns("D:D").Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

With Range("F15") 'Pretty sure this Range is wrong, but it was the last Selection
.Replace What:="AB", Replacement:="AQ"
.Replace What:="W1", Replacement:="W"
.Replace What:="B3", Replacement:="BN"
.Replace What:="CB", Replacement:="BL"
.Replace What:="B1", Replacement:="BLK"
.Replace What:="B4", Replacement:="NB"
.Replace What:="P1", Replacement:="P"
.Replace What:="P2", Replacement:="PU"
.Replace What:="B2", Replacement:="C"
.Replace What:="C1", Replacement:="CHO"
End With

Range("E2").FormulaR1C1 = "=CONCATENATE(RC[-3],""-"",RC[-1])"
Range("E2").AutoFill Destination:=Range("E2:E1054")
Range("C1").Copy Range("E1")
Application.CutCopyMode = False

Columns("E:E").Copy
Columns("C:C").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Columns("D:F").Delete Shift:=xlToLeft
End Sub

[impr]

Hello,

Thank you so much for your help

However when i use your code on my sheet or on the blank sheet i had attached, i get a error 1004

when i click debug i see that the error is at:


Columns("D:D").Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False


Can you please advise what i need to fix?

Thank you for your help in advance