Hello
Could anyone show me how to change the following macro to activie sheet/tab rather than having to create a new macro for every tab.

Sub runlocal()

'

' Reset local

iRejCnt = 0

iTotDRVal = 0

iTotCRVal = 0

iRejAdd = 0

Application.ScreenUpdating = False

' Underline and count relevant lines

rwIndex = 1

Do Until Worksheets("local").Cells(rwIndex, 1).Value = ""



' Check if current line is a rejection

ActiveSheet.Cells(rwIndex, 1).Select

bRejItem = False: bDRItem = False: bCntBal = True: iRejAdd = 1

sline = Worksheets("local").Cells(rwIndex, 1).Value

If InStr(1, sline, "REJECTED TRANSACTION", 1) Then bRejItem = True: iRejAdd = 1

If InStr(1, sline, "INVALID TRANSACTION", 1) Then bRejItem = True: iRejAdd = 1

If InStr(1, sline, "EARLY SETTLEMENT OF", 1) Then bRejItem = False: bCntBal = True: iRejAdd = 1

If InStr(1, sline, "CURRENT SETTLEMENT", 1) Then bRejItem = True: bCntBal = False: iRejAdd = 1

If InStr(1, sline, "PARTIAL PAYMENT", 1) Then bRejItem = True: bCntBal = True: iRejAdd = 1

If InStr(1, sline, "REJECTED DUE TO REBATE DISCREPANCY", 1) Then bRejItem = True: iRejAdd = 1

If InStr(1, sline, "REJECTED TRANSACTION PARTIAL", 1) Then bRejItem = True: iRejAdd = 0

If InStr(1, sline, "ACCOUNT TOTAL TO DATE", 1) Then bRejItem = False: iRejAdd = 0: bCntBal = False

If InStr(1, sline, "FEES IN TRANSIT", 1) Then bRejItem = False: iRejAdd = 0: bCntBal = False

If InStr(1, sline, "REBATES IN TRANSIT", 1) Then bRejItem = False: iRejAdd = 0: bCntBal = False

If InStr(1, sline, "INTEREST IN TRANSIT", 1) Then bRejItem = False: iRejAdd = 0: bCntBal = False

If InStr(1, sline, "PREMIUM IN TRANSIT", 1) Then bRejItem = False: iRejAdd = 0: bCntBal = False

If InStr(1, sline, "LEDGER BALANCE", 1) Then bRejItem = False: iRejAdd = 0: bCntBal = False

If InStr(1, sline, "THE BALANCE", 1) Then bRejItem = False: iRejAdd = 0: bCntBal = False

If InStr(1, sline, "TODAYS TRANSACTION", 1) Then bRejItem = False: iRejAdd = 0: bCntBal = False

If InStr(1, sline, "CREDITOR INTEREST", 1) Then bRejItem = False: iRejAdd = 0: bCntBal = False

If InStr(1, sline, "DIFFERENCE", 1) Then bRejItem = False: iRejAdd = 0: bCntBal = False

If InStr(1, sline, "INITIALS", 1) Then bRejItem = False: iRejAdd = 0: bCntBal = False

If InStr(37, sline, "DR", 1) Then bRejItem = True: bDRItem = True

' Calculate figure to add to balancing totals

If bCntBal = True Then

sRejValue = "": bFndNum = False

sline = Selection.Value

For iExtNum = 40 To Len(sline)

sLineExt = Mid$(sline, iExtNum, 1)

If sLineExt = Chr(46) And sLineExt <= Chr(57) And bFndNum = False Then sRejValue = sRejValue & sLineExt

If sLineExt Chr(57) And sRejValue < "" Then bFndNum = True

Next iExtNum

If bRejItem = False Then iTotCRVal = iTotCRVal + Val(sRejValue)

End If



' Underline report line

If bRejItem = True Then

LASTROW = rwIndex

iRejCnt = iRejCnt + iRejAdd

Selection.Borders(xlEdgeBottom).Weight = xlHairline

If bDRItem = True Then

Selection.Interior.ColorIndex = 35

If bCntBal = True Then iTotDRVal = iTotDRVal + Val(sRejValue)

Else

Selection.Interior.ColorIndex = xlNone

If bCntBal = True Then iTotCRVal = iTotCRVal + Val(sRejValue)

End If

If iRejCnt 0 And iRejCnt / 20 = Int(iRejCnt / 20) Then Range("B" & rwIndex) = iRejCnt

End If



rwIndex = rwIndex + 1

Loop

Range("W2") = rwIndex - 1

' Total of CR/DR for bottom of printout

Range("A" & rwIndex) = "Total CR Value = " & iTotDRVal

Range("A" & rwIndex + 1) = "Total DR Value = " & iTotCRVal

Range("T2") = iTotCRVal

Range("S2") = iTotDRVal

Range("x2") = LASTROW - 1

'

End Sub


Thank you in Advance

CR

"lostgrave2001" wrote:
Could anyone show me how to change the following macro to activie
sheet/tab rather than having to create a new macro for every tab.
[....]
Do Until Worksheets("local").Cells(rwIndex, 1).Value = ""
[....]
ActiveSheet.Cells(rwIndex, 1).Select
[....]
sline = Worksheets("local").Cells(rwIndex, 1).Value
[....]
Range("W2") = rwIndex - 1

I have not studied your code in detail. But I suspect you entered into a
worksheet object, for example by right-clicking on the worksheet tab and
clicking on View Code.

If that is the case, in VBA, click on Insert, then Module. Then cut
(ctrl+X) the text from the sheet object and paste it into the module.

You might also want to remove references to Worksheets("local"), unless your
intent is to reference a worksheet that might not be the active worksheet.

And the use of ActiveSheet appears to be redundant, or it needs to be
changed, depending on your intent.

You might need to understand the distinction among the various ways to refer
to worksheets implicitly and explicitly.

Suppose the code is currently in the Sheet1 object, the ActiveSheet is
Sheet2, and the worksheet "local" is Sheet3. That is, there are three
different worksheets involved.

Then Range("W2") is equivalent to Sheet1.Range("W2"), ActiveSheet.Cells is
equivalent to Sheet2.Cells, and Worksheets("local").Cells is equivalent to
Sheet3.Cells.

If you simply cut-and-paste the code into a normal module without change,
Range("W2") will be equivalent to Sheet2.Range("W2"). All the other
equivalent references would be the same.

Hello again,

i have tried to edit my code to ecxept "ActiveSheet.Cells(rwIndex, 1).Select" but when i try to change out " Worksheets("Local"

Can someone please help me out please.

Thank you

Hi,

Am Mon, 5 Aug 2013 21:38:44 +0100 schrieb lostgrave2001:

i have tried to edit my code to ecxept "ActiveSheet.Cells(rwIndex,
1).Select" but when i try to change out " Worksheets("Local"

you want to run the code on all sheets? Then try:
dim wsh as worksheet

Application.ScreenUpdating = False
For Each wsh In ActiveWorkbook.Worksheets
your code
next wsh
And change into the code every "Worksheets("local")" and every
"ActiveSheet" to wsh

Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

There would be 4 sheets i couldnt have the macro run on.

Kind regards

Chris

"lostgrave2001" wrote:
There would be 4 sheets i couldnt have the macro run on.

Your requirements are no longer clear to me.

Based on your original posting, I assumed you wanted to run a macro
__manually__ against any active worksheet. You simply wanted to know how to
make the macro available to all worksheets, and what coding changes might be
needed.

Claus assumed you wanted a macro that you would run once and it applied its
algorithm to some number of worksheets. Claus's loop selected all
worksheets. Your response indicates that you want all but 4 worksheets.

In either case, you indicated that you had difficulty applying the changes I
suggested. I assume you would have similar difficulties integrated those
changes with Claus's suggest. The changes are similar, but not exactly the
same.

If you still want help with this, please indicate which solution you want:
one macro that you run manually for any active worksheet; or a loop like
Claus's, but avoiding certain worksheets.

And please post the modified code, based on my suggestions, that did not
seem to work for you.

Finally, please let us know where the macro code currently resides: a
worksheet (object) module, located by right-clicking on the worksheet tab
and clicking on View Code; or a normal worksheet module, created by clicking
on Insert, then Module.