|
Macro hangs when run from a command button
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 |
Macro hangs when run from a command button
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 |
Macro hangs when run from a command button
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. "Nigel" wrote: 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 |
Macro hangs when run from a command button
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 |
Macro hangs when run from a command button
Up-date:- Code stops at Range ("C4").Activate (4th line)(highligthed in yellow)
"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 |
Macro hangs when run from a command button
is the userform with "commandbuton1" in the same workbook as the sheet
"vehicle records" ??? "Nigel" wrote: 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 |
Macro hangs when run from a command button
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. "LeShark" wrote: is the userform with "commandbuton1" in the same workbook as the sheet "vehicle records" ??? "Nigel" wrote: 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 |
Macro hangs when run from a command button
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 |
Macro hangs when run from a command button
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 |
Macro hangs when run from a command button
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 |
Macro hangs when run from a command button
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 |
Macro hangs when run from a command button
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 |
| All times are GMT +1. The time now is 12:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com