400 Error during Macro

  • Thread starter Thread starter DFrank
  • Start date Start date
D

DFrank

Hello, I am running a macro that removes cells with nothing in them, sorts
them, and then moves them to another worksheet. here it is:

Public Sub DeleteStuff()
Cells.SpecialCells(xlCellTypeFormulas, xlErrors).Delete
Range("D1:F9").Select
Selection.Sort Key1:=Range("D1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Sheets("PROPOSAL").Select
ActiveCell.FormulaR1C1 = "=MidStep!R[-19]C[3]"
Range("C21").Select
Selection.AutoFill Destination:=Range("C21:C68"), Type:=xlFillDefault
Range("C21:C68").Select
ActiveWindow.SmallScroll Down:=-42
End Sub



It deletes and sorts alright, but when it goes to copy to the other
worksheet, a Microsoft Visual Basic error box comes up, and it only says 400
inside. Any ideas about what this is? i couldnt find anything here pertaining
to it. Thanks for any help.
 
but when it goes to copy to the other worksheet

But there is no Copy in your code.

Re this code:
Sheets("PROPOSAL").Select
ActiveCell.FormulaR1C1 = "=MidStep!R[-19]C[3]"

This isn't so good because it depends on the right cell having been
preselected on sheet Proposal. Better to be specific:

Range("B20").FormulaR1C1 = "=MidStep!R[-19]C[3]"

--
Jim
| Hello, I am running a macro that removes cells with nothing in them, sorts
| them, and then moves them to another worksheet. here it is:
|
| Public Sub DeleteStuff()
| Cells.SpecialCells(xlCellTypeFormulas, xlErrors).Delete
| Range("D1:F9").Select
| Selection.Sort Key1:=Range("D1"), Order1:=xlAscending, Header:=xlGuess,
_
| OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
| DataOption1:=xlSortNormal
|
| Sheets("PROPOSAL").Select
| ActiveCell.FormulaR1C1 = "=MidStep!R[-19]C[3]"
| Range("C21").Select
| Selection.AutoFill Destination:=Range("C21:C68"), Type:=xlFillDefault
| Range("C21:C68").Select
| ActiveWindow.SmallScroll Down:=-42
| End Sub
|
|
|
| It deletes and sorts alright, but when it goes to copy to the other
| worksheet, a Microsoft Visual Basic error box comes up, and it only says
400
| inside. Any ideas about what this is? i couldnt find anything here
pertaining
| to it. Thanks for any help.
 
Back
Top