I replicated your code (without the autoopen macro as I dont know what that
does.

It worked fine when called from a button on the worksheet.

This is an ordinary "Command button" to which I assigned the macro - not a
control button.

Hope this helps



"Nigel" wrote:

Sorry! I meant that i saw the correct name and then i pressed OK. Then the
code continued to run for two further lines and then stopped where it says
Private Sub CommandButton1_Click()
Workbooks.Open "C:\Documents and Settings\All Users\Documents\Innovate
Damage Log.xls"
ActiveWorkbook.RunAutoMacros xlAutoOpen
MsgBox ActiveWorkbook.Name
Worksheets("VEHICLE RECORDS").Activate
Range("C4").Activate RIGHT THERE

"LeShark" wrote:

now u are really confusing me

when u put in the MSGBOX did you see the correct workbook name???

did u then change some code???

if you did not make any change the code should have stopped in the usual
place not 2 rows further down



"Nigel" wrote:

Tried your suggestion but it still hangs two rows further down but thanks
anyway

"LeShark" wrote:

stick this line into the code after the AutoOpen macro line

msgbox activeworkbook.name

this will confirm if the correct workbook is active for the worksheets line



"Nigel" wrote:

Private Sub CommandButton1_Click()
Workbooks.Open "C:\Documents and Settings\All Users\Documents\Innovate
Damage Log.xls"
ActiveWorkbook.RunAutoMacros xlAutoOpen
Worksheets("VEHICLE RECORDS").Activate
Range("C4").Activate
Set tbl = ActiveCell.CurrentRegion
ActiveCell.CurrentRegion.Copy
ActiveSheet.Paste Destination:=Workbooks("Damage Log
Analysis.xls").Worksheets("Slave1").Range("A1")
Application.CutCopyMode = False
Workbooks("Innovate Damage Log.xls").Worksheets("VEHICLE
RECORDS").Activate
Range("P4").Activate
Set tbl = ActiveCell.CurrentRegion
ActiveCell.CurrentRegion.Copy
ActiveSheet.Paste Destination:=Workbooks("Damage Log
Analysis.xls").Worksheets("Slave2").Range("A1")
Application.CutCopyMode = False
Workbooks("Innovate Damage Log.xls").Worksheets("INPUT DAMAGE").Activate
Range("AR15").Activate
Set tbl = ActiveCell.CurrentRegion
tbl.Offset(2, 0).Resize(tbl.Rows.Count - 2, _
tbl.Columns.Count).Copy
ActiveSheet.Paste Destination:=Workbooks("Damage Log
Analysis.xls").Worksheets("Slave3").Range("A2")
Application.CutCopyMode = False
Workbooks("Innovate Damage Log.xls").Close SaveChanges:=False
Worksheets("Slave3").Activate
Worksheets("Slave3").Range("L2:L7").Copy
Range("M2").Activate
ActiveCell.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Worksheets("Slave1").Select
Set currentCell = Worksheets("Slave1").Range("A2")
Do While Not IsEmpty(currentCell)
Set nextCell = currentCell.Offset(0, 10)
nextCell.FormulaR1C1 =
"=IF(MONTH(RC[-4])=MONTH(R1C12)-1,""M"","" "")"
Set currentCell = nextCell.Offset(1, -10)
Loop
Range("I4").Select
Selection.Sort Key1:=Range("J2"), Order1:=xlAscending, Key2:=Range("K2") _
, Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
Set currentCell = Worksheets("Slave1").Range("A2")
Do While Not IsEmpty(currentCell)
Set nextCell = currentCell.Offset(1, 9)
If Not nextCell.Value = "D" Then
currentCell.EntireRow.Delete
End If
Set currentCell = nextCell.Offset(0, -9)
Loop
Set currentCell = Worksheets("Slave1").Range("A2")
Do While Not IsEmpty(currentCell)
Set nextCell = currentCell.Offset(1, 10)
If Not nextCell.Value = "M" Then
currentCell.EntireRow.Delete
End If
Set currentCell = nextCell.Offset(0, -10)
Loop
Worksheets("Slave2").Select
Set currentCell = Worksheets("Slave2").Range("A2")
Do While Not IsEmpty(currentCell)
Set nextCell = currentCell.Offset(0, 11)
nextCell.FormulaR1C1 =
"=IF(MONTH(RC[-4])=MONTH(R1C13)-1,""M"","" "")"
Set currentCell = nextCell.Offset(1, -11)
Loop
Range("J4").Select
Selection.Sort Key1:=Range("K2"), Order1:=xlAscending, Key2:=Range("L2") _
, Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
Set currentCell = Worksheets("Slave2").Range("A2")
Do While Not IsEmpty(currentCell)
Set nextCell = currentCell.Offset(1, 10)
If Not nextCell.Value = "D" Then
currentCell.EntireRow.Delete
End If
Set currentCell = nextCell.Offset(0, -10)
Loop
Set currentCell = Worksheets("Slave2").Range("A2")
Do While Not IsEmpty(currentCell)
Set nextCell = currentCell.Offset(1, 11)
If Not nextCell.Value = "M" Then
currentCell.EntireRow.Delete
End If
Set currentCell = nextCell.Offset(0, -11)
Loop
Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
Sheets("Master Report").Select
Range("A1:R35").Select
Selection.Copy
Worksheets("Sheet1").Activate
Range("A1").Activate
ActiveCell.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Columns("A:A").ColumnWidth = 16.14
Columns("B:B").ColumnWidth = 4.29
Columns("C:C").ColumnWidth = 4.29
Columns("F:F").ColumnWidth = 4.29
Columns("I:I").ColumnWidth = 4.29
Columns("J:J").ColumnWidth = 4.29
Columns("M:M").ColumnWidth = 4.29
Columns("P:P").ColumnWidth = 4.29
Columns("D:D").ColumnWidth = 7.57
Columns("G:G").ColumnWidth = 7.57
Columns("K:K").ColumnWidth = 7.57
Columns("N:N").ColumnWidth = 7.57
Columns("Q:Q").ColumnWidth = 7.57
Columns("E:E").ColumnWidth = 7.86
Columns("H:H").ColumnWidth = 7.86
Columns("L:L").ColumnWidth = 7.86
Columns("O:O").ColumnWidth = 7.86
Columns("R:R").ColumnWidth = 7.86
Sheets("Sheet1").Name = Range("L1")
Range("A1").Select


End Sub

"Bob Phillips" wrote:

Post the code perhaps, give us a chance.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Nigel" wrote in message
...
A macro that i've recorded/edited/written works fine when run from VB
editor/run sub user form or keyboard shortcut but not when I assign the
code
to a (Controls) button, however it will run from a (Forms) button.
I recieve the error code 1004 Application-defined or object defined error.
The code stops at Range ("R4").Select

If anyone can help i would be gratefull, i am not an IT Professional

Nigel