Please, help. Run-time Error 1004: Application-defined or object-defined. Excel 2007

O

Oxy

New Member
Joined
Jan 5, 2011
Messages
3
Hello Everyone,

I wonder if someone can help me.
I have the Macro below. I've changed as it was not displaying the results properly, and since fixing that problem I keep receiveing the 'Run-time error 1004: Application-defined or object-defined error' every time I try to run this macro on a new customer.
I've changed the colour into red bold to show the place where it always refers me to whenever I debug. It's basically the place which I've changed.

I'd highly appreciate your help. It's my assignment and I should have it done very soon.

Thank you to everyone.


Sub PT_macro_1()
'
' PT_macro_1 Macro
'
' setup
MsgBox "The macro will now run, please be patient and do not close Excel", vbInformation
Application.StatusBar = "MACRO IN PROGRESS - PLEASE DO NOT CLOSE"
Application.ScreenUpdating = False

' Clear and unformat summary
Columns("S:S").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll Down:=15
Rows("55:55").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Range("E12").Select
Range("E12:I1000").Select
Selection.ClearContents
Selection.Borders.LineStyle = xlNone
Selection.HorizontalAlignment = xlGeneral
With Selection.Font
.Name = "Arial"
.Size = 10
.Bold = False
.Italic = False
End With
Range("L16:Q1000").Select
Selection.ClearContents
Selection.Borders.LineStyle = xlNone
Selection.HorizontalAlignment = xlGeneral
With Selection.Font
.Name = "Arial"
.Size = 10
.Bold = False
.Italic = False
End With

' Data import
Sheets("Q&O Data").Select
ActiveSheet.Range("$A$1:$T$105632").AutoFilter Field:=8, Criteria1:="1"
Columns("B:K").Select
Selection.EntireColumn.Hidden = True
Columns("O:R").Select
Selection.EntireColumn.Hidden = True
Range("A1").Select
Selection.CurrentRegion.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Customer").Select
Range("E12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=True, Transpose:=False
Sheets("Sales Cycles Data").Select
ActiveSheet.Range("$A$1:$W$31192").AutoFilter Field:=3, Criteria1:="1"
ActiveWindow.ScrollColumn = 15
ActiveSheet.Range("$A$1:$W$31192").AutoFilter Field:=23, Criteria1:="="
ActiveWindow.ScrollColumn = 1
Columns("A:H").Select
Selection.EntireColumn.Hidden = True
Columns("J:P").Select
Selection.EntireColumn.Hidden = True
Columns("S:S").Select
Selection.EntireColumn.Hidden = True
Columns("T:T").Select
Selection.EntireColumn.Hidden = True
Columns("W:W").Select
Selection.EntireColumn.Hidden = True
Columns("V:V").Select
Selection.EntireColumn.Hidden = True
Range("I1").Select
Selection.CurrentRegion.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Customer").Select
Application.CutCopyMode = False
Sheets("Sales Cycles Data").Select
Selection.Copy
Sheets("Customer").Select
Range("l16").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=True, Transpose:=False
Range("O16").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, -3).Select
 ActiveCell.FormulaR1C1 = "Vehicles in Order Bank"
Sheets("Order Bank").Select
ActiveWindow.ScrollColumn = 1
ActiveSheet.Range("$A$1:$W$1000").AutoFilter Field:=5, Criteria1:="1"
Columns("A:I").Select
Range("I1").Activate
Selection.EntireColumn.Hidden = True
Columns("O:T").Select
Selection.EntireColumn.Hidden = True
Columns("V:W").Select
Selection.EntireColumn.Hidden = True
Range("J1").Select
Selection.CurrentRegion.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Customer").Select
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=True, Transpose:=False
ActiveCell.Offset(1, 0).Select
Selection.End(xlDown).Select
Range("L15").Select
Application.CutCopyMode = False
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "Funded Fleet not in Sales Cycles"
Sheets("Fund Flt new").Select
ActiveSheet.Range("$A$1:$W$8985").AutoFilter Field:=5, Criteria1:="1"
Columns("A:I").Select
Range("I1").Activate
Selection.EntireColumn.Hidden = True
Columns("N:Q").Select
Selection.EntireColumn.Hidden = True
Columns("S:X").Select
Selection.EntireColumn.Hidden = True
Range("J1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Customer").Select
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=True, Transpose:=False

' Formatting

Range("E12").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlUp)).Select
Application.CutCopyMode = False
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("L15").Select
Selection.CurrentRegion.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.CurrentRegion.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.CurrentRegion.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("L15").Select
Sheets("Customer").Select
Range("E12").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "m/d/yyyy"
Range("I12").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "$#,##0.00"
Range("O15").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "m/d/yyyy"
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "m/d/yyyy"
Range("P15").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "m/d/yyyy"
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "$#,##0.00"
Range("Q15").Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "$#,##0.00"
Range("E11").Select
Selection.Font.Bold = True
Selection.Font.Italic = True
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Font.Italic = True
Range("L15").Select
Selection.Font.Bold = True
Selection.Font.Italic = True
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Font.Italic = True
Range("O16").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, -3).Select
Selection.Font.Bold = True
Selection.Font.Italic = True
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Font.Italic = True
Range("L15").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.Font.Bold = True
Selection.Font.Italic = True
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Font.Italic = True
Selection.Font.Bold = False
Columns("M:Q").EntireColumn.AutoFit
Range("L9").Select

' Closing functions

Application.ScreenUpdating = True
Application.StatusBar = False
Range("A1").Select
End Sub
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
You must log in or register to reply here.

Similar threads

L
  • Locked
  • Question Question
Help Cleaning Up Code
Replies
2
Views
591
RoryA
RoryA
K
  • Solved
formating question
Replies
3
Views
324
Micron
M
P
  • Question Question
Issue with button not running in spread sheet only in test mode
Replies
1
Views
378
6StringJazzer
6StringJazzer
K
  • Solved
create a button that do actions
2 3
Replies
22
Views
1K
kerm007
K
J
  • Question Question
Issue with Macros and renaming files
Replies
6
Views
1K
kevin9999
K

Forum statistics

Threads
1,224,503
Messages
6,179,134
Members
452,890
Latest member
Nikhil Ramesh

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top