Can anyone help me tidy up?

D

drucey

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)=TRUE),"""",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,FALSE))"
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,FALSE)=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)=TRUE),"""",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,FALSE))"
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)=TRUE),"""",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,FALSE))"
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,FALSE)=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)=TRUE),"""",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,FALSE))"


'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
 
A

Abode

Im pretty new at programming for excel too. I started with being amased that
I could have excel add two cells together. Ive been working on a sub that
will combine several worksheets together everytime I need to update and it
seriously cuts my work time in half. Im currently working on a custom
sorting sub thats gotten rather large and complicated. Anyway, I am still
new and I cant really help you cleaning up the code.

But one thing I do to help troubleshoot code (which makes it a lot easier
to update the code too) is throw all the code that outputs into a different
sub that is called by the main and have a Global variable (boolean) that when
I turn it on will have all the outputting subs Exit before any changes are
made and possably put some of the data into a msgbox so you can make sure
everything is running properly.

Another thing that is useful is to have a variable that increments one
number all over the program. If I run into a problem I can tell VBA to break
when that variable changes values and hit F5 until I reach the code that Im
having problems on and F8 through that section to see exactly what the
program is doing.

Those methods and a few others work for me. It may be worth a shot to try
them out if updating and troubleshooting your program is a big concern. (If
you want I can post a small example of what Im talking about if what I said
makes little sense)
 
D

Doug Glancy

drucey,

One of the key pieces of advice you'll see in this newsgroup is to eliminate
"Select"s and "Activate"s wherever possible - and it's almost always
possible. This speeds up your code a lot. For example, change:

Range("F2").Select
ActiveCell.Formula = "=MID(C3,4,1)"

to:

Range("F2").Formula = "=MID(C3,4,1)"

hth,

Doug
 

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

Need help-For loop 3
Six columns randon no.'s no dupes 6
vlookup issue 2
Copy filtered data to sheet 2 1
test post no need to reply 3
Code does not error but no copy either 4
Need help to fix vlookup function in macro 1
Excel hangs 1

Top