Don't laugh! I've spent weeks doing this (my first proper macro!) and am
so pleased with it!

But, it's rather large.

Any tidying up possible?

Thankyou!


Code:
--------------------
Sub Pick()

'Open a CAP file
ChDrive "J"
ChDir "J:\Accounts\Cap\"
fname = Application.GetOpenFilename()
Workbooks.OpenText Filename:=fname, Origin _
:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(0, 1) _
, TrailingMinusNumbers:=True

'Remember the CAP name
Dim CapName As Variant
CapName = ActiveWorkbook.Name



'Find the beginning of useful data
Cells.Find(What:="end of day report for day", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate

'Get rid of all the junk data
Dim L As Long
L = Selection.Row - 3
If L 0 Then
s = "1:" & L
Rows(s).Select
Selection.Delete Shift:=xlUp
End If

'Add appropriate column seperators
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(23, 1), Array(32, 1)), TrailingMinusNumbers _
:=True

'Select all and copy to Workbook
Cells.Select
Selection.Copy
Windows("Stores.xls").Activate
Sheets("Temp").Select
Range("A1").Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Windows(CapName).Close
Application.DisplayAlerts = True

'Extract the date
Range("F1").Select
ActiveCell.Formula = "=MID(A1,5,11)"
Range("F1").Select
Dim CapDate As Date
CapDate = Range("F1").Value

'Extract the Till number
Range("F2").Select
ActiveCell.Formula = "=MID(C3,4,1)"
Range("F2").Select
Dim TillNumber As Integer
TillNumber = Range("F2").Value

'Find the Time & Value data
Cells.Find(What:="timeband report", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Dim rng As Range

'Copy the Time & Value data
Set rng = ActiveCell.Offset(4, 1).Resize(24, 2)
rng.Copy

'In main workbook, find which column to paste data into
Windows("Stores.xls").Activate
Sheets("Till0" & TillNumber).Select
Range("A1").Select

'Set the first Zero row
Cells.Find(What:="0000", LookIn:=xlValues, SearchFormat:=False).Activate
Dim Zero As Variant
Zero = ActiveCell.Row

'Set the first payment method row
Cells.Find(What:="Account Sales", LookIn:=xlValues, SearchFormat:=False).Activate
Dim AccSales As Variant
AccSales = ActiveCell.Row

'Set the first discount row
Cells.Find(What:="item discount", LookIn:=xlValues, SearchFormat:=False).Activate
Dim Discounts As Variant
Discounts = ActiveCell.Row

'Search for the Cap Date
Cells.Find(What:=CapDate, LookIn:=xlFormulas, SearchFormat:=False).Activate
Dim CapDateCol As Variant
CapDateCol = ActiveCell.Column

'Paste the Till value and data
Worksheets("Till0" & TillNumber).Cells(Zero, CapDateCol).Select
ActiveSheet.Paste

'Lookup Payment Method data
Worksheets("Temp").Activate
Range("F10").Select
ActiveCell.Formula = "=IF(ISNA(VLOOKUP(""ACCOUNT SALES"",Temp!A10:C20,2,FALSE)=TRUE),"""",VLOOKUP(" "ACCOUNT SALES"",Temp!A10:C20,2,FALSE))"
ActiveCell.Offset(1, 0).Formula = "=IF(ISNA(VLOOKUP(""CASH"",Temp!A10:C20,2,FALSE)=T RUE),"""",VLOOKUP(""CASH"",Temp!A10:C20,2,FALSE))"
ActiveCell.Offset(2, 0).Formula = "=IF(ISNA(VLOOKUP(""CHEQUE"",Temp!A10:C20,2,FALSE) =TRUE),"""",VLOOKUP(""CHEQUE"",Temp!A10:C20,2,FALS E))"
ActiveCell.Offset(3, 0).Formula = "=IF(ISNA(VLOOKUP(""CREDIT NOTE"",Temp!A10:C20,2,FALSE)=TRUE),"""",VLOOKUP("" CREDIT NOTE"",Temp!A10:C20,2,FALSE))"
ActiveCell.Offset(4, 0).Formula = "=IF(ISNA(VLOOKUP(""DELTA CARD"",Temp!A10:C20,2,FALSE)=TRUE),"""",VLOOKUP("" DELTA CARD"",Temp!A10:C20,2,FALSE))"
ActiveCell.Offset(5, 0).Formula = "=IF(ISNA(VLOOKUP(""ELECTRON"",Temp!A10:C20,2,FALS E)=TRUE),"""",VLOOKUP(""ELECTRON"",Temp!A10:C20,2, FALSE))"
ActiveCell.Offset(6, 0).Formula = "=IF(ISNA(VLOOKUP(""MASTER CARD"",Temp!A10:C20,2,FALSE)=TRUE),"""",VLOOKUP("" MASTER CARD"",Temp!A10:C20,2,FALSE))"
ActiveCell.Offset(7, 0).Formula = "=IF(ISNA(VLOOKUP(""SOLO"",Temp!A10:C20,2,FALSE)=T RUE),"""",VLOOKUP(""SOLO"",Temp!A10:C20,2,FALSE))"
ActiveCell.Offset(8, 0).Formula = "=IF(ISNA(VLOOKUP(""SWITCH CARD"",Temp!A10:C20,2,FALSE)=TRUE),"""",VLOOKUP("" SWITCH CARD"",Temp!A10:C20,2,FALSE))"
ActiveCell.Offset(9, 0).Formula = "=IF(ISNA(VLOOKUP(""VISA CARD"",Temp!A10:C20,2,FALSE)=TRUE),"""",VLOOKUP("" VISA CARD"",Temp!A10:C20,2,FALSE))"
ActiveCell.Offset(10, 0).Formula = "=IF(ISNA(VLOOKUP(""VOUCHER"",Temp!A10:C20,2,FALSE )=TRUE),"""",VLOOKUP(""VOUCHER"",Temp!A10:C20,2,FA LSE))"
ActiveCell.Offset(0, 1).Formula = "=IF(ISNA(VLOOKUP(""ACCOUNT SALES"",Temp!A10:C20,3,FALSE)=TRUE),"""",VLOOKUP(" "ACCOUNT SALES"",Temp!A10:C20,3,FALSE))"
ActiveCell.Offset(1, 1).Formula = "=IF(ISNA(VLOOKUP(""CASH"",Temp!A10:C20,3,FALSE)=T RUE),"""",VLOOKUP(""CASH"",Temp!A10:C20,3,FALSE))"
ActiveCell.Offset(2, 1).Formula = "=IF(ISNA(VLOOKUP(""CHEQUE"",Temp!A10:C20,3,FALSE) =TRUE),"""",VLOOKUP(""CHEQUE"",Temp!A10:C20,3,FALS E))"
ActiveCell.Offset(3, 1).Formula = "=IF(ISNA(VLOOKUP(""CREDIT NOTE"",Temp!A10:C20,3,FALSE)=TRUE),"""",VLOOKUP("" CREDIT NOTE"",Temp!A10:C20,3,FALSE))"
ActiveCell.Offset(4, 1).Formula = "=IF(ISNA(VLOOKUP(""DELTA CARD"",Temp!A10:C20,3,FALSE)=TRUE),"""",VLOOKUP("" DELTA CARD"",Temp!A10:C20,3,FALSE))"
ActiveCell.Offset(5, 1).Formula = "=IF(ISNA(VLOOKUP(""ELECTRON"",Temp!A10:C20,3,FALS E)=TRUE),"""",VLOOKUP(""ELECTRON"",Temp!A10:C20,3, FALSE))"
ActiveCell.Offset(6, 1).Formula = "=IF(ISNA(VLOOKUP(""MASTER CARD"",Temp!A10:C20,3,FALSE)=TRUE),"""",VLOOKUP("" MASTER CARD"",Temp!A10:C20,3,FALSE))"
ActiveCell.Offset(7, 1).Formula = "=IF(ISNA(VLOOKUP(""SOLO"",Temp!A10:C20,3,FALSE)=T RUE),"""",VLOOKUP(""SOLO"",Temp!A10:C20,3,FALSE))"
ActiveCell.Offset(8, 1).Formula = "=IF(ISNA(VLOOKUP(""SWITCH CARD"",Temp!A10:C20,3,FALSE)=TRUE),"""",VLOOKUP("" SWITCH CARD"",Temp!A10:C20,3,FALSE))"
ActiveCell.Offset(9, 1).Formula = "=IF(ISNA(VLOOKUP(""VISA CARD"",Temp!A10:C20,3,FALSE)=TRUE),"""",VLOOKUP("" VISA CARD"",Temp!A10:C20,3,FALSE))"
ActiveCell.Offset(10, 1).Formula = "=IF(ISNA(VLOOKUP(""VOUCHER"",Temp!A10:C20,3,FALSE )=TRUE),"""",VLOOKUP(""VOUCHER"",Temp!A10:C20,3,FA LSE))"


'Copy Payment Method data to summary

Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Value = Worksheets("Temp").Cells(10, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(1, 0).Value = Worksheets("Temp").Cells(11, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(2, 0).Value = Worksheets("Temp").Cells(12, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(3, 0).Value = Worksheets("Temp").Cells(13, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(4, 0).Value = Worksheets("Temp").Cells(14, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(5, 0).Value = Worksheets("Temp").Cells(15, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(6, 0).Value = Worksheets("Temp").Cells(16, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(7, 0).Value = Worksheets("Temp").Cells(17, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(8, 0).Value = Worksheets("Temp").Cells(18, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(9, 0).Value = Worksheets("Temp").Cells(19, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(10, 0).Value = Worksheets("Temp").Cells(20, 6).Value

Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(0, 1).Value = Worksheets("Temp").Cells(10, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(1, 1).Value = Worksheets("Temp").Cells(11, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(2, 1).Value = Worksheets("Temp").Cells(12, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(3, 1).Value = Worksheets("Temp").Cells(13, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(4, 1).Value = Worksheets("Temp").Cells(14, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(5, 1).Value = Worksheets("Temp").Cells(15, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(6, 1).Value = Worksheets("Temp").Cells(16, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(7, 1).Value = Worksheets("Temp").Cells(17, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(8, 1).Value = Worksheets("Temp").Cells(18, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(9, 1).Value = Worksheets("Temp").Cells(19, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(10, 1).Value = Worksheets("Temp").Cells(20, 7).Value



--------------------


PART 2 Below


--
drucey
------------------------------------------------------------------------
drucey's Profile: http://www.excelforum.com/member.php...o&userid=32553
View this thread: http://www.excelforum.com/showthread...hreadid=539306