This is a piece of simple code I have in a macro at the moment -


Code:
--------------------
If Application.Sum(Range("L6")) 0 Then
Application.ActivePrinter = "\\bristol\Canon CLC4040-H1 PS Ver1.0 2 on Ne04:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"\\bristol\Canon CLC4040-H1 PS Ver1.0 2 on Ne04:", Collate:=True
End If

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


I need to change this code so that it loops around in some way.

The cell the code is currently looking at ("L6") needs to change to
("N6").

It needs to look at N6 in Sheets - Bic_Code01 then Bic_Code02 etc up to
Bic Code_20.

If ("N6") is equal to one of the following codes on any of these sheets
-

ABA
AB
AC
ACQ
WH
A
ACX
AKLP
AGB
ABQ
AKC
AVGM
ABC
APF
AFPC
ACV
AFY
JFH
AFC
AFF

Print the Page if not move to the next sheet, look at N6 and repeat the
process.

So basically look at N6 on every sheet in the workbook and if it equals
any of the codes print the page.

I have to do this for 28 files all with 20 sheets in all with different
codes.

There won't always be 20 codes there might be just six so stop after
the last code.

Any help much appreciated.


--
Timbo
------------------------------------------------------------------------
Timbo's Profile: http://www.thecodecage.com/forumz/member.php?userid=24
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=110050

Hi Timbo

This should do it. I have just entered some codes as an example, it should
be easy to add all

Sub PrintSomeSheets()
Dim PrintCodes As Variant
Dim PrintPage As Boolean
PrintCodes = Split("ABA,AB,AC,ACQ,WH,A,ACX", ",") ' Here goes all codes
Application.ActivePrinter = "\\bristol\Canon CLC4040-H1 PS Ver1.0 2 on
Ne04:"

For Each sh In ActiveWorkbook.Sheets
For c = LBound(PrintCodes) To UBound(PrintCodes)
If PrintCodes(c) = sh.Range("N6") Then
PrintPage = True
Exit For
End If
Next
If PrintPage = True Then
sh.PrintOut Copies:=1, ActivePrinter:= _
"\\bristol\Canon CLC4040-H1 PS Ver1.0 2 on Ne04:",
Collate:=True
End If
PrintPage = False
Next
End Sub

Best regards,
Per

"Timbo" skrev i meddelelsen
...

This is a piece of simple code I have in a macro at the moment -


Code:
--------------------
If Application.Sum(Range("L6")) 0 Then
Application.ActivePrinter = "\\bristol\Canon CLC4040-H1 PS Ver1.0 2 on
Ne04:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"\\bristol\Canon CLC4040-H1 PS Ver1.0 2 on Ne04:", Collate:=True
End If

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


I need to change this code so that it loops around in some way.

The cell the code is currently looking at ("L6") needs to change to
("N6").

It needs to look at N6 in Sheets - Bic_Code01 then Bic_Code02 etc up to
Bic Code_20.

If ("N6") is equal to one of the following codes on any of these sheets
-

ABA
AB
AC
ACQ
WH
A
ACX
AKLP
AGB
ABQ
AKC
AVGM
ABC
APF
AFPC
ACV
AFY
JFH
AFC
AFF

Print the Page if not move to the next sheet, look at N6 and repeat the
process.

So basically look at N6 on every sheet in the workbook and if it equals
any of the codes print the page.

I have to do this for 28 files all with 20 sheets in all with different
codes.

There won't always be 20 codes there might be just six so stop after
the last code.

Any help much appreciated.


--
Timbo
------------------------------------------------------------------------
Timbo's Profile: http://www.thecodecage.com/forumz/member.php?userid=24
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=110050

The code your provided yesterday works a treat many thanks. Is it
possible to change this part of the code


Code:
--------------------
If PrintCodes(c) = sh.Range("N6") Then
--------------------


to this


Code:
--------------------
If PrintCodes(c) = sh.Range("N:N") Then
--------------------


?

My reason for asking is that I have found that on a minority of the
worksheets the code is appearing further down the worksheet not in a
specific cell.


--
Timbo
------------------------------------------------------------------------
Timbo's Profile: http://www.thecodecage.com/forumz/member.php?userid=24
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=110050

Thanks for your reply.

You have to test the column cell by cell.

For each cell In sh.columns("N")
if cell.value=PrintCodes(c) then
PrintPage = True
Exit For
End If
Next

But there are other options...

If PrintCodeCell is the only or downmost cell in the column, you can use
this:

if PrintCodes(c)=sh.Range("N" & Rows.Count).End(xlup) then

or you use excel 2007, you can name each print code cell as "PrintCode" and
set the scope to the sheet.

Then you can use this:

If PrintCodes(c)=sh.Range("PrintCode") then

Regards,
Per


"Timbo" skrev i meddelelsen
...

The code your provided yesterday works a treat many thanks. Is it
possible to change this part of the code


Code:
--------------------
If PrintCodes(c) = sh.Range("N6") Then
--------------------


to this


Code:
--------------------
If PrintCodes(c) = sh.Range("N:N") Then
--------------------


?

My reason for asking is that I have found that on a minority of the
worksheets the code is appearing further down the worksheet not in a
specific cell.


--
Timbo
------------------------------------------------------------------------
Timbo's Profile: http://www.thecodecage.com/forumz/member.php?userid=24
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=110050

I want to find the code which will be in column N anywhere N6 and N200,
which would be the best option for that? I don't have access to Excel
2007.


--
Timbo
------------------------------------------------------------------------
Timbo's Profile: http://www.thecodecage.com/forumz/member.php?userid=24
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=110050

Hi Timbo

You could loop through the cells as I suggested in my last post or you can
use Find, which I think is more efficient...

Sub PrintSomeSheets()
Dim PrintCodes As Variant
Dim PrintPage As Boolean
Dim CodeRange As Range
Dim Found As Variant

PrintCodes = Split("ABA,AB,AC,ACQ,WH,A,ACX", ",") ' Here goes all codes
Application.ActivePrinter = "\\bristol\Canon CLC4040-H1 PS Ver1.0 2 on
Ne04:"

For Each sh In ActiveWorkbook.Sheets
Set CodeRange = sh.Range("N6:N200")
For c = LBound(PrintCodes) To UBound(PrintCodes)
Set Found = CodeRange.Find(what:=PrintCodes(c),
After:=sh.Range("N200"), Lookat:=xlWhole)
If Not Found Is Nothing Then
PrintPage = True
Exit For
End If
Next
If PrintPage = True Then
sh.PrintOut Copies:=1, ActivePrinter:= _
"\\bristol\Canon CLC4040-H1 PS Ver1.0 2 on Ne04:",
Collate:=True
End If
PrintPage = False
Next
End Sub

Regards,
Per

"Timbo" skrev i meddelelsen
...

I want to find the code which will be in column N anywhere N6 and N200,
which would be the best option for that? I don't have access to Excel
2007.


--
Timbo
------------------------------------------------------------------------
Timbo's Profile: http://www.thecodecage.com/forumz/member.php?userid=24
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=110050

Thanks Per brilliant!


--
Timbo
------------------------------------------------------------------------
Timbo's Profile: http://www.thecodecage.com/forumz/member.php?userid=24
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=110050