Excel previous worksheet

[dmccallum]

I maintain payment records, one sheet per customer. I want to be able to find the last payment made, calculate the time difference between today and the last payment made, and copy those individuals names and addresses to the last sheet named "BP". My formula works for the first sheet but after I have pasted the info from the first sheet to BP I can't see how to get back to the second sheet, third, etc. So far it goes like this:

Sub LastCell()
'
' LastCell Macro
' Macro recorded 4/27/2001 by D. McCallum

For Each sht In Workbooks("BCLS.xlw&quot.Worksheets

Selection.SpecialCells(xlCellTypeLastCell).Select
ActiveCell.Offset(0, -254).Range("A1&quot.Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1&quot.Select
ActiveCell.FormulaR1C1 = "=TODAY()-R[-1]C[1]"
ActiveCell.Offset(1, 0).Range("A1&quot.Select


If ActiveCell.FormulaR1C1 > 35 Then
Rows("9:10&quot.Select
Range("A10&quot.Activate
Selection.Copy
Sheets("BP&quot.Select
Selection.Insert Shift:=xlDown
Range("A3&quot.Select
End If

Next sht

End Sub

Eventually I would like to be able to have Word create the payment late letter for for now I just need help in getting around the sheets.

 

[DarkSun]

First you want to change the code in the If statement to this:

sht.Rows("9:10&quot.Copy
Sheets("BP&quot.Rows("1:1&quot.Insert Shift:=xlDown

I tested your code and it had no problem moving through all the sheets.

Try it with my code and let me know what happens.

 

[dmccallum]

Your input was just what I needed. I've made some modifications in the effort of efficiency and for a while it was not going to the next sheet but it's doing much better now.

Sub FindBal()
'
' FindBal Macro
' Macro recorded 5/1/2001 by D. McCallum
'
For Each sht In Workbooks("BCLS.xls&quot.Worksheets

'
Selection.SpecialCells(xlCellTypeLastCell).Select
Selection.End(xlToLeft).Select
ActiveCell.Offset(0, 1).Range("A1&quot.Select
Selection.End(xlUp).Select
ActiveCell.Offset(0, 7).Range("A1&quot.Select
Bal = ActiveCell.Value
Ans = MsgBox("Balance is ", , Bal)
If Bal > 0 Then
Selection.SpecialCells(xlCellTypeLastCell).Select
ActiveCell.Offset(0, -254).Range("A1&quot.Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 1).Range("A1&quot.Select
ActiveCell.FormulaR1C1 = "=TODAY()"
ActiveCell.Offset(-1, 1).Range("A1&quot.Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1&quot.Select
ActiveSheet.Paste
Days = ActiveCell.Value
Ans = MsgBox("Days is ", , Days)
If Days > 35 Then
sht.Rows("9:11&quot.Copy
Sheets("BP&quot.Rows("1:1&quot.Insert Shift:=xlDown
End If
End If
Next sht
End Sub

I now check for a zero balance and then check for the days since last payment. Do you have any further suggestions and do you know how to get the list I'm creating into Word for a mail merge?

 

[DarkSun]

Once you have it doing what you want it to do, you could email me an example of the data and the word document and I could explain how to set up Mail Merge.

 

[DarkSun]

Sorry, My address is dark_suns@lycos.com