Something in this is causing a crash
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...
--
HTH...
Jim Thomlinson
"Simon" wrote:
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
|