Macro hangs when run from a command button

N

Nigel

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
 
B

Bob Phillips

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)
 
J

Joel

I think you need a referrence tto the sheet. Instead of

from
Range ("R4").Select
to
sheets("sheet1).Range ("R4").Select

Your code is probably expecting a certttain worksheet to be active and you
are runnintg the code from the wrong worksheet. Check your code and make
sure you are referrencing each sheet properly by including a sheets in all
your references.
 
N

Nigel

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
 
L

LeShark

is the userform with "commandbuton1" in the same workbook as the sheet
"vehicle records" ???
 
N

Nigel

No. The command button runs the code from the first book "Damage Log
Analysis" which opens the second book "Innovate Damage Log", copies some data
to the first book and then closes the second book.
 
L

LeShark

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 said:
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 said:
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)
Click to expand...
Click to expand...
 
N

Nigel

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

LeShark said:
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 said:
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 said:
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)



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
Click to expand...
Click to expand...
Click to expand...
 
L

LeShark

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 said:
Tried your suggestion but it still hangs two rows further down but thanks
anyway

LeShark said:
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 said:
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

:

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)



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
Click to expand...
Click to expand...
Click to expand...
 
N

Nigel

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 said:
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 said:
Tried your suggestion but it still hangs two rows further down but thanks
anyway

LeShark said:
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



:

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

:

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)



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
Click to expand...
Click to expand...
Click to expand...
 
L

LeShark

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 said:
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 said:
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 said:
Tried your suggestion but it still hangs two rows further down but thanks
anyway

:

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



:

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

:

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)



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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I get an Excel macro to run completely using the keyboard? 5
Command button to run a macro 3
Closing FIle with Command Bar Button Problem 3
Macro attached to Menu Command Button 2
run macro in another workbook - error 1004 7
Macro / Sort error 4
Attempting to call a sub from another wb using a Userform option b 2
I protect sheet - Macro will not run now 7

Top