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