Something in this is causing a crash

S

Simon

Sub Accountsreportv7macro()

' AccountsReportv7macro Macro
' Macro written 24/04/2008 by axuklslav
'
Dim MyWBRBS As Workbook
Dim MyWBSEL As Workbook
Dim MyWBNEWSEL As Workbook
Dim MyWBHEX As Workbook
Dim MyWBNEWDAL As Workbook
Dim MyWBAccRep As Workbook
Dim MyWBConvertor As Workbook
' Dim MyWBConvertor4 As Workbook

'MsgBox "Check 'latest' in row 3 of AccountsReport is in the correct
location i.e. latest month to be reported"
'If MsgBox("WARNING date will flow incorrectly if 'latest' in wrong
position, continue?", vbYesNo) = vbNo Then
'End
'End If

Set MyWBConvertor = Workbooks.Open("N:\mis\\Reporting\Latest.xls")

MyWBConvertor.Activate
Dim MyPath3 As String, MyRange3 As Range
Dim MyPath4 As String, MyRange4 As Range
MyPath3 = MyWBConvertor.Path
MyPath4 = MyWBConvertor.Path
Set MyRange3 = MyWBConvertor.ActiveSheet.Range("D25") '2 months ago
Set MyRange4 = MyWBConvertor.ActiveSheet.Range("C26") 'Year

MyWBConvertor.Activate
Dim MyPath As String, MyRange As Range
Dim MyPath2 As String, MyRange2 As Range
MyPath = MyWBConvertor.Path
MyPath2 = MyWBConvertor.Path
Set MyRange = MyWBConvertor.ActiveSheet.Range("C25") ' Last month
Set MyRange2 = MyWBConvertor.ActiveSheet.Range("C26") ' Year

Set MyWBRBS = Workbooks.Open("N:\mis\Reporting\" & MyRange3.Value &
MyRange4.Value & "\" & "RBS Figures" & " " & MyRange3.Value &
MyRange4.Value & ".xls")
Set MyWBSEL = Workbooks.Open("N:\mis\" & MyRange.Value &
MyRange2.Value & "\" & "SEL Figures New Version" & " " & MyRange.Value
& MyRange2.Value & ".xls")
Set MyWBNEWSEL = Workbooks.Open("N:\mis\" & MyRange.Value &
MyRange2.Value & "\" & "NEW-SEL Billing" & " " & MyRange.Value &
MyRange2.Value & ".xls")
Set MyWBHEX = Workbooks.Open("N:\mis\Reporting\" & MyRange3.Value &
MyRange4.Value & "\" & "HEX Figures" & " " & MyRange3.Value &
MyRange4.Value & ".xls")
Set MyWBNEWDAL = Workbooks.Open("N:\mis\Reporting\" & MyRange3.Value &
MyRange4.Value & "\" & "New-DAL Billing" & " " & MyRange3.Value &
MyRange4.Value & ".xls")
Set MyWBAccRep = Workbooks.Open("N:\mis\Reporting\" & MyRange.Value &
MyRange2.Value & "\" & "AccountsReport" & " " & MyRange.Value &
MyRange2.Value & ".xls")

MyWBAccRep.Activate
Cells.Find("latest").Select
Selection.Copy
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
ActiveCell.Offset(0, -1).Activate
ActiveCell.Clear

MyWBRBS.Activate
Range("I4").Select
Selection.End(xlDown).Select
Selection.Copy
MyWBAccRep.Activate
ActiveCell.Select
Cells.Find("latest").Select
ActiveCell.Offset(7, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

MyWBRBS.Activate
Range("M25").Select
Selection.End(xlDown).Select
Selection.Copy
MyWBAccRep.Activate
Cells.Find("latest").Select
ActiveCell.Offset(8, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

MyWBSEL.Activate
Sheets("AA").Select
Range("G4").Select
Selection.End(xlDown).Select
Selection.Copy
MyWBAccRep.Activate
Cells.Find("latest").Select
ActiveCell.Offset(12, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

MyWBSEL.Activate
Sheets("Allianz").Select
Range("G4").Select
Selection.End(xlDown).Select
Selection.Copy
MyWBAccRep.Activate
Cells.Find("latest").Select
ActiveCell.Offset(13, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

'MyWBSEL.Activate
' Sheets("AXA").Select
' Range("G4").Select
' Selection.End(xlDown).Select
'' Application.CutCopyMode = False
' Selection.Copy
'MyWBAccRep.Activate
' Cells.Find("latest").Select
' ActiveCell.Offset(14, 0).Select
' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False


MyWBSEL.Activate
Sheets("Elite").Select
Range("G4").Select
Selection.End(xlDown).Select
Application.CutCopyMode = False
Selection.Copy
MyWBAccRep.Activate
Cells.Find("latest").Select
ActiveCell.Offset(21, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

MyWBSEL.Activate
Sheets("Chaucer").Select
Range("H4").Select
Selection.End(xlDown).Select
Application.CutCopyMode = False
Selection.Copy
MyWBAccRep.Activate
Cells.Find("latest").Select
ActiveCell.Offset(22, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

MyWBSEL.Activate
Sheets("Motorcare").Select
Range("G4").Select
Selection.End(xlDown).Select
Application.CutCopyMode = False
Selection.Copy
MyWBAccRep.Activate
Cells.Find("latest").Select
ActiveCell.Offset(23, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

MyWBSEL.Activate
Sheets("LINKZEN").Select
Range("G4").Select
Selection.End(xlDown).Select
Application.CutCopyMode = False
Selection.Copy
MyWBAccRep.Activate
Cells.Find("latest").Select
ActiveCell.Offset(25, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

'MsgBox ("HelpHire value for Row 29 comes from ftp mdb using SEL
Billing Calculation sheet (PartsOut=0 etc), filter by selection for
HelpHire total")

MyWBSEL.Activate
Sheets("HelpHire").Select
Range("H4").Select
Selection.End(xlDown).Select
Application.CutCopyMode = False
Selection.Copy
MyWBAccRep.Activate
Cells.Find("latest").Select
ActiveCell.Offset(27, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

MyWBSEL.Activate
Sheets("SIMS").Select
Range("H4").Select
Selection.End(xlDown).Select
Application.CutCopyMode = False
Selection.Copy
MyWBAccRep.Activate
Cells.Find("latest").Select
ActiveCell.Offset(26, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
MyWBSEL.Close
'''''''''''''''''''''''''''''''''''''''''''''''''
'Below is the Insurer in New
SEL Billing
'AA
'''''''''''''''''''''''''''''''''''''''''''''''''
MyWBNEWSEL.Activate
Range("J4").Select
Selection.End(xlDown).Select
Selection.Offset(-1, 0).Select
Selection.Copy
MyWBAccRep.Activate
Cells.Find("latest").Select
ActiveCell.Offset(28, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
'Allianz
MyWBNEWSEL.Activate
Range("K4").Select
Selection.End(xlDown).Select
Selection.Offset(-1, 0).Select
Application.CutCopyMode = False
Selection.Copy
MyWBAccRep.Activate
Cells.Find("latest").Select
ActiveCell.Offset(32, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
'Chaucer
MyWBNEWSEL.Activate
Range("L4").Select
Selection.End(xlDown).Select
Selection.Offset(-1, 0).Select
Application.CutCopyMode = False
Selection.Copy
MyWBAccRep.Activate
Cells.Find("latest").Select
ActiveCell.Offset(33, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
'Elite
MyWBNEWSEL.Activate
Range("M4").Select
Selection.End(xlDown).Select
Selection.Offset(-1, 0).Select
Application.CutCopyMode = False
Selection.Copy
MyWBAccRep.Activate
Cells.Find("latest").Select
ActiveCell.Offset(30, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
'HelpHire
MyWBNEWSEL.Activate
Range("N24").Select
Selection.End(xlDown).Select
Selection.Offset(-1, 0).Select
Application.CutCopyMode = False
Selection.Copy
MyWBAccRep.Activate
Cells.Find("latest").Select
ActiveCell.Offset(36, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
'Rubicon
MyWBNEWSEL.Activate
Range("O4").Select
Selection.End(xlDown).Select
Selection.Offset(-1, 0).Select
Application.CutCopyMode = False
Selection.Copy
MyWBAccRep.Activate
Cells.Find("latest").Select
ActiveCell.Offset(34, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
'SIMS
MyWBNEWSEL.Activate
Range("P24").Select
Selection.End(xlDown).Select
Selection.Offset(-1, 0).Select
Application.CutCopyMode = False
Selection.Copy
MyWBAccRep.Activate
Cells.Find("latest").Select
ActiveCell.Offset(35, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

' MyWBNEWSEL.Activate
' Range("P24").Select 'Range?
' Selection.End(xlDown).Select
' Selection.Offset(-1, 0).Select
' Application.CutCopyMode = False
' Selection.Copy
'MyWBAccRep.Activate
' Cells.Find("latest").Select
' ActiveCell.Offset(37, 0).Select
' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
'
' MyWBNEWSEL.Activate
' Range("P24").Select 'Range?
' Selection.End(xlDown).Select
' Selection.Offset(-1, 0).Select
' Application.CutCopyMode = False
' Selection.Copy
'MyWBAccRep.Activate
' Cells.Find("latest").Select
' ActiveCell.Offset(38, 0).Select
' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

' MyWBNEWSEL.Activate
' Range("P24").Select 'Range?
' Selection.End(xlDown).Select
' Selection.Offset(-1, 0).Select
' Application.CutCopyMode = False
' Selection.Copy
'MyWBAccRep.Activate
' Cells.Find("latest").Select
' ActiveCell.Offset(39, 0).Select
' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False'

'Total Estimates
MyWBNEWSEL.Activate
Range("S4").Select
Selection.End(xlDown).Select
Selection.Offset(-1, 0).Select
Application.CutCopyMode = False
Selection.Copy
MyWBAccRep.Activate
Cells.Find("latest").Select
ActiveCell.Offset(40, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

MyWBNEWSEL.Activate
Range("F4").Select
Selection.End(xlDown).Select
Application.CutCopyMode = False
Selection.Copy
MyWBAccRep.Activate
Cells.Find("latest").Select
ActiveCell.Offset(41, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

MyWBNEWSEL.Activate
Range("G11").Select
Selection.End(xlDown).Select
Application.CutCopyMode = False
Selection.Copy
MyWBAccRep.Activate
Cells.Find("latest").Select
ActiveCell.Offset(42, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
MyWBNEWSEL.Close SaveChanges:=False
'HEX - Repairer scheme - processed
MyWBHEX.Activate
Range("E4").Select
Selection.End(xlDown).Select
Selection.Offset(-1, 0).Copy
' Application.CutCopyMode = False
' Selection.Copy
MyWBAccRep.Activate
Cells.Find("latest").Select
ActiveCell.Offset(44, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
'HEX - Repairer scheme - sent by FTP
MyWBHEX.Activate
Range("D4").Select
Selection.End(xlDown).Select
Selection.Offset(-1, 0).Copy
MyWBAccRep.Activate
Cells.Find("latest").Select
ActiveCell.Offset(45, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
MyWBHEX.Close
'DAL/Repairer 0 - 2000 Assessments
MyWBNEWDAL.Activate
Range("E4").Select
Selection.End(xlDown).Select
Selection.Offset(-1, 0).Copy
MyWBAccRep.Activate
Cells.Find("latest").Select
ActiveCell.Offset(47, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
'DAl/Repairer - 2001 and above
MyWBNEWDAL.Activate
Range("F4").Select
Selection.End(xlDown).Select
Selection.Offset(-1, 0).Copy
MyWBAccRep.Activate
Cells.Find("latest").Select
ActiveCell.Offset(48, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
'DAL Allianz
MyWBNEWDAL.Activate
Range("I20").Select
Selection.End(xlDown).Select
Application.CutCopyMode = False
Selection.Copy
MyWBAccRep.Activate
Cells.Find("latest").Select
ActiveCell.Offset(49, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

' DAL(AXA)
'
'MyWBNEWDAL.Activate
' Range("I20").Select
' Selection.End(xlDown).Select
' Application.CutCopyMode = False
' Selection.Copy
' MyWBAccRep.Activate
' Cells.Find("latest").Select
' ActiveCell.Offset(49, 0).Select
' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
MyWBNEWDAL.Close

'Can be ChDir depending on whether the macro has been ran at least
once already (MkDir will create the new month and would have to amend
to use ChDir from then on)
'ChDir "N:\mis\Reporting\" & Format(Now(), "MMYYYY")
' ActiveWorkbook.SaveAs "N:\mis\Reporting\" & Range("##").Value &
".xls"


MyWBAccRep.Save
MyWBConvertor.Close SaveChanges:=False

End Sub
 
J

Jim Thomlinson

Have you tried stepping thorugh the code. Where does it crash??? There is no
way for us to trouble shoot that much code that we can not possibly run...
 
J

J Sedoff comRemove>

Not really able to help on fixing the crash problem.. As the other Jim
pointed out, please run the code with several different break points to
determine what line is causing it to crash, rather than the whole bundle (I
couldn't find anything that is glaringly wrong).

As for future coding, you can change string concatenations such as:
Set MyWBRBS = Workbooks.Open("N:\mis\Reporting\" & MyRange3.Value &
MyRange4.Value & "\" & "RBS Figures" & " " & MyRange3.Value &
MyRange4.Value & ".xls")

to this:
Set MyWBRBS = Workbooks.Open("N:\mis\Reporting\" & MyRange3.Value &
MyRange4.Value & "\RBS Figures " & MyRange3.Value &
MyRange4.Value & ".xls")

That is, merge several strings together.
"This can" & " " & "be one" & " string." --> "This can be one string."

Hope this helps, Jim
 

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

What in the edited code below makes it crash????? 2
Run-time error 1004 paste method of worksheet class failed 3
conso macro 0
Copy paste - Values - formula - Optimise 2
Simplify Code for Copy/Paste Special 11
File Name changes with dates and Deleting Rows of Zeros 0
Variable not defined for "number of trials" ? 3
lenghty code 4

Top