Looking for a method to print only those worksheets that the user has entered data into

[rpw]

Hi everyone

I have a workbook that has 7 worksheets un-hidden. Of those, each has a limited number of cells that are un-protected. Each sheet prints to a single page. The user can enter data on one, some or all of the sheets. Is there a short-cut method that would allow the user to (at a single click) print all of the sheets that have user-input data and not any of those that are left 'blank'

TI

rp

 

[Frank Kabel]

Hi
not possible without VBA

--
Regards
Frank Kabel
Frankfurt, Germany

Hi everyone,

I have a workbook that has 7 worksheets un-hidden. Of those, each

has a limited number of cells that are un-protected. Each sheet prints
to a single page. The user can enter data on one, some or all of the
sheets. Is there a short-cut method that would allow the user to (at a
single click) print all of the sheets that have user-input data and not
any of those that are left 'blank'?

 

[rpw]

Hi Frank

Thanks for the quick response. I'm not very skilled at VBA. I know where to edit it, and I've learned how to modify generic code so it matches my applications, but when it comes to actually creating it, I'm really lacking in skill. If you have a solution you could post that'd be great. On the other hand, if you'd like to take the time to 'guide' me through the process, I do have an idea of what needs to happen

I would imagine that to start off I need to create a module, then maybe place a control on the main worksheet that runs the module. I guess the module code would then make each worksheet active, search a defined range for anything other than blanks, print the page if there's data, skip the printing if there's no data, and go to the next worksheet and run through the cycle again

TI

rp

----- Frank Kabel wrote: ----

H
not possible without VB

-
Regard
Frank Kabe
Frankfurt, German

Hi everyone

has a limited number of cells that are un-protected. Each sheet print
to a single page. The user can enter data on one, some or all of th
sheets. Is there a short-cut method that would allow the user to (at
single click) print all of the sheets that have user-input data and no
any of those that are left 'blank'

 

[rpw]

Ummm, being sort of impatient I went and started a macro that selects the various worksheets and ranges in the order I want. So it looks like the next step is to determine if there's any data in the range. How do I do that?

Here's what's in the macro so far....

Option Explicit

Sub PrintDataWkShts()
'
' PrintDataWkShts Macro
' Macro recorded 5/26/2004 by rpw
'
' Keyboard Shortcut: Ctrl+w
'
Sheets("Basic Pricing").Select
Range("D131176").Select
Range("I131:I176").Select
Range("N131:N176").Select
Range("S131:S176").Select
Sheets("Quick Price Sections").Select
Range("Y17:Y72").Select
Sheets("Quick Price Accessories").Select
Range("P1778").Select
Sheets("Accessories Area (1)").Select
Range("C130:I190").Select
Sheets("Accessories Area (2)").Select
Range("C130:I190").Select
Sheets("Accessories Area (3)").Select
Range("C130:I190").Select
Sheets("Accessories Area (4)").Select
Range("C130:I190").Select
Sheets("Basic Pricing").Select
Range("D3").Activate
End Sub

----- rpw wrote: -----

Hi Frank,

Thanks for the quick response. I'm not very skilled at VBA. I know where to edit it, and I've learned how to modify generic code so it matches my applications, but when it comes to actually creating it, I'm really lacking in skill. If you have a solution you could post that'd be great. On the other hand, if you'd like to take the time to 'guide' me through the process, I do have an idea of what needs to happen.

I would imagine that to start off I need to create a module, then maybe place a control on the main worksheet that runs the module. I guess the module code would then make each worksheet active, search a defined range for anything other than blanks, print the page if there's data, skip the printing if there's no data, and go to the next worksheet and run through the cycle again?

TIA

rpw

----- Frank Kabel wrote: -----

Hi
not possible without VBA

--
Regards
Frank Kabel
Frankfurt, Germany

Hi everyone,

has a limited number of cells that are un-protected. Each sheet prints
to a single page. The user can enter data on one, some or all of the
sheets. Is there a short-cut method that would allow the user to (at a
single click) print all of the sheets that have user-input data and not
any of those that are left 'blank'?

 

[Frank Kabel]

Hi
try something like the following (not tested though)
sub print_it
dim wks as worksheet
dim rng as range
dim fillcells

for each wks in worksheets
set rng=wks.range("A1:S1000")
fillcells=application.worksheetfunction.countA(rng)
if fillcells>0 then
wks.printout
end if
next
end sub


--
Regards
Frank Kabel
Frankfurt, Germany

Ummm, being sort of impatient I went and started a macro that selects

the various worksheets and ranges in the order I want. So it looks
like the next step is to determine if there's any data in the range.
How do I do that?

Here's what's in the macro so far....

Option Explicit

Sub PrintDataWkShts()
'
' PrintDataWkShts Macro
' Macro recorded 5/26/2004 by rpw
'
' Keyboard Shortcut: Ctrl+w
'
Sheets("Basic Pricing").Select
Range("D131176").Select
Range("I131:I176").Select
Range("N131:N176").Select
Range("S131:S176").Select
Sheets("Quick Price Sections").Select
Range("Y17:Y72").Select
Sheets("Quick Price Accessories").Select
Range("P1778").Select
Sheets("Accessories Area (1)").Select
Range("C130:I190").Select
Sheets("Accessories Area (2)").Select
Range("C130:I190").Select
Sheets("Accessories Area (3)").Select
Range("C130:I190").Select
Sheets("Accessories Area (4)").Select
Range("C130:I190").Select
Sheets("Basic Pricing").Select
Range("D3").Activate
End Sub

----- rpw wrote: -----

Hi Frank,

Thanks for the quick response. I'm not very skilled at VBA. I

know where to edit it, and I've learned how to modify generic code so
it matches my applications, but when it comes to actually creating it,
I'm really lacking in skill. If you have a solution you could post
that'd be great. On the other hand, if you'd like to take the time to
'guide' me through the process, I do have an idea of what needs to
happen.

I would imagine that to start off I need to create a module,

then maybe place a control on the main worksheet that runs the module.
I guess the module code would then make each worksheet active, search a
defined range for anything other than blanks, print the page if there's
data, skip the printing if there's no data, and go to the next
worksheet and run through the cycle again?

 

[rpw]

Hi Frank

Thanks for your input. I looked up and tested the 'CountA' function and it counts even formulas (at least in a cell formula). This makes a generic range for each sheet not workable for me. So it looks like I'm going to have to list each worksheet and their ranges separately in the code. However, while attempting to do that I ran into another problem. Some of the sheets have multiple ranges. How do you get multiple ranges

I've tried: ("A1:A70"), ("D170"
("A1:A70", "D170"
("A1:A70" + "D170"
("A1:A70") +("D170"
("A1:A70, D170"
and get compile errors

Hmmm, as I'm writing this I'm thinking that the variable 'rng' could be set to the first range and then set again to the second range plus rng........ Nope, tried it and that doesn't work

So, how do I get it to look at multiple ranges on a single worksheet

Thanks again for taking the time to help

rp


----- Frank Kabel wrote: ----

H
try something like the following (not tested though
sub print_i
dim wks as workshee
dim rng as rang
dim fillcell

for each wks in worksheet
set rng=wks.range("A1:S1000"
fillcells=application.worksheetfunction.countA(rng
if fillcells>0 the
wks.printou
end i
nex
end su


-
Regard
Frank Kabe
Frankfurt, German

Ummm, being sort of impatient I went and started a macro that select

the various worksheets and ranges in the order I want. So it look
like the next step is to determine if there's any data in the range
How do I do that

' PrintDataWkShts Macr
' Macro recorded 5/26/2004 by rp

' Keyboard Shortcut: Ctrl+

Sheets("Basic Pricing").Selec
Range("D131176").Selec
Range("I131:I176").Selec
Range("N131:N176").Selec
Range("S131:S176").Selec
Sheets("Quick Price Sections").Selec
Range("Y17:Y72").Selec
Sheets("Quick Price Accessories").Selec
Range("P1778").Selec
Sheets("Accessories Area (1)").Selec
Range("C130:I190").Selec
Sheets("Accessories Area (2)").Selec
Range("C130:I190").Selec
Sheets("Accessories Area (3)").Selec
Range("C130:I190").Selec
Sheets("Accessories Area (4)").Selec
Range("C130:I190").Selec
Sheets("Basic Pricing").Selec
Range("D3").Activat
End Su

know where to edit it, and I've learned how to modify generic code s
it matches my applications, but when it comes to actually creating it
I'm really lacking in skill. If you have a solution you could pos
that'd be great. On the other hand, if you'd like to take the time t
'guide' me through the process, I do have an idea of what needs t
happenthen maybe place a control on the main worksheet that runs the module
I guess the module code would then make each worksheet active, search
defined range for anything other than blanks, print the page if there'
data, skip the printing if there's no data, and go to the nex
worksheet and run through the cycle again

TI
rp
----- Frank Kabel wrote: ----
H not possible without VB
-

Regard
Frank Kabe
Frankfurt, German

 

[rpw]

Hello again

I've got it working now. It's not as elegant as your suggestion, but you definately gave me some good clues on getting it done

I ended up adding one "fillcells" to the previous "fillcells" for each worksheet range

Here's the (incomplete) code in case anyone reading this thread has suggestions for improvement

Option Explici

Sub PrintDataWkShts(

' PrintDataWkShts Macr
' Keyboard Shortcut: Ctrl+
Dim rng As Rang
Dim fillcells As Intege

Sheets("Basic Pricing").Selec
Set rng = ActiveSheet.Range("D131176"
fillcells = Application.WorksheetFunction.CountA(rng
Set rng = ActiveSheet.Range("I131:I176"
fillcells = Application.WorksheetFunction.CountA(rng) + fillcell
If fillcells > 0 The
ActiveSheet.PrintOu
End I
fillcells =
Sheets("Quick Price Sections").Selec
Set rng = ActiveSheet.Range("D1772"
fillcells = WorksheetFunction.CountA(rng
If fillcells > 0 The
ActiveSheet.PrintOu
End I
fillcells =
Sheets("Quick Price Accessories").Selec
Set rng = ActiveSheet.Range("P1778"
fillcells = WorksheetFunction.CountA(rng
If fillcells > 0 The
ActiveSheet.PrintOu
End I
Sheets("Accessories Area (1)").Selec
Range("C130:I190").Selec
Sheets("Accessories Area (2)").Selec
Range("C130:I190").Selec
Sheets("Accessories Area (3)").Selec
Range("C130:I190").Selec
Sheets("Accessories Area (4)").Selec
Range("C130:I190").Selec
Sheets("Basic Pricing").Selec
Range("D3").Activat
End Su

Thanks again for your help

rp

----- rpw wrote: ----

Hi Frank

Thanks for your input. I looked up and tested the 'CountA' function and it counts even formulas (at least in a cell formula). This makes a generic range for each sheet not workable for me. So it looks like I'm going to have to list each worksheet and their ranges separately in the code. However, while attempting to do that I ran into another problem. Some of the sheets have multiple ranges. How do you get multiple ranges

I've tried: ("A1:A70"), ("D170"
("A1:A70", "D170"
("A1:A70" + "D170"
("A1:A70") +("D170"
("A1:A70, D170"
and get compile errors

Hmmm, as I'm writing this I'm thinking that the variable 'rng' could be set to the first range and then set again to the second range plus rng........ Nope, tried it and that doesn't work

So, how do I get it to look at multiple ranges on a single worksheet

Thanks again for taking the time to help

rp


----- Frank Kabel wrote: ----

H
try something like the following (not tested though
sub print_i
dim wks as workshee
dim rng as rang
dim fillcell

for each wks in worksheet
set rng=wks.range("A1:S1000"
fillcells=application.worksheetfunction.countA(rng
if fillcells>0 the
wks.printou
end i
nex
end su


-
Regard
Frank Kabe
Frankfurt, German

Ummm, being sort of impatient I went and started a macro that select

the various worksheets and ranges in the order I want. So it look
like the next step is to determine if there's any data in the range
How do I do that

' PrintDataWkShts Macr
' Macro recorded 5/26/2004 by rp

' Keyboard Shortcut: Ctrl+

Sheets("Basic Pricing").Selec
Range("D131176").Selec
Range("I131:I176").Select
Range("N131:N176").Select
Range("S131:S176").Select
Sheets("Quick Price Sections").Select
Range("Y17:Y72").Select
Sheets("Quick Price Accessories").Select
Range("P1778").Select
Sheets("Accessories Area (1)").Select
Range("C130:I190").Select
Sheets("Accessories Area (2)").Select
Range("C130:I190").Select
Sheets("Accessories Area (3)").Select
Range("C130:I190").Select
Sheets("Accessories Area (4)").Select
Range("C130:I190").Select
Sheets("Basic Pricing").Select
Range("D3").Activate
End Sub

know where to edit it, and I've learned how to modify generic code so
it matches my applications, but when it comes to actually creating it,
I'm really lacking in skill. If you have a solution you could post
that'd be great. On the other hand, if you'd like to take the time to
'guide' me through the process, I do have an idea of what needs to
happen.then maybe place a control on the main worksheet that runs the module.
I guess the module code would then make each worksheet active, search a
defined range for anything other than blanks, print the page if there's
data, skip the printing if there's no data, and go to the next
worksheet and run through the cycle again?

TIA
rpw
----- Frank Kabel wrote: -----
Hi not possible without VBA
--

Regards
Frank Kabel
Frankfurt, Germany

 

[Frank Kabel]

Hi
have a look at the Union method or use multiple counta statements and
add them together

--
Regards
Frank Kabel
Frankfurt, Germany

Hi Frank,

Thanks for your input. I looked up and tested the 'CountA' function

and it counts even formulas (at least in a cell formula). This makes a
generic range for each sheet not workable for me. So it looks like I'm
going to have to list each worksheet and their ranges separately in the
code. However, while attempting to do that I ran into another problem.
Some of the sheets have multiple ranges. How do you get multiple
ranges?

I've tried: ("A1:A70"), ("D170")
("A1:A70", "D170")
("A1:A70" + "D170")
("A1:A70") +("D170")
("A1:A70, D170")
and get compile errors.

Hmmm, as I'm writing this I'm thinking that the variable 'rng' could

be set to the first range and then set again to the second range plus
rng........ Nope, tried it and that doesn't work.

So, how do I get it to look at multiple ranges on a single worksheet?

Thanks again for taking the time to help.

rpw


----- Frank Kabel wrote: -----

Hi
try something like the following (not tested though)
sub print_it
dim wks as worksheet
dim rng as range
dim fillcells

for each wks in worksheets
set rng=wks.range("A1:S1000")
fillcells=application.worksheetfunction.countA(rng)
if fillcells>0 then
wks.printout
end if
next
end sub


--
Regards
Frank Kabel
Frankfurt, Germany

Ummm, being sort of impatient I went and started a macro that

selects
the various worksheets and ranges in the order I want. So it looks
like the next step is to determine if there's any data in the range.
How do I do that?

'
' PrintDataWkShts Macro
' Macro recorded 5/26/2004 by rpw
'
' Keyboard Shortcut: Ctrl+w
'
Sheets("Basic Pricing").Select
Range("D131176").Select
Range("I131:I176").Select
Range("N131:N176").Select
Range("S131:S176").Select
Sheets("Quick Price Sections").Select
Range("Y17:Y72").Select
Sheets("Quick Price Accessories").Select
Range("P1778").Select
Sheets("Accessories Area (1)").Select
Range("C130:I190").Select
Sheets("Accessories Area (2)").Select
Range("C130:I190").Select
Sheets("Accessories Area (3)").Select
Range("C130:I190").Select
Sheets("Accessories Area (4)").Select
Range("C130:I190").Select
Sheets("Basic Pricing").Select
Range("D3").Activate
End Sub

VBA. I
know where to edit it, and I've learned how to modify generic code so
it matches my applications, but when it comes to actually creating it,
I'm really lacking in skill. If you have a solution you could post
that'd be great. On the other hand, if you'd like to take the time to
'guide' me through the process, I do have an idea of what needs to

happen.

module,
then maybe place a control on the main worksheet that runs the module.
I guess the module code would then make each worksheet active, search a
defined range for anything other than blanks, print the page if there's
data, skip the printing if there's no data, and go to the next
worksheet and run through the cycle again?

TIA
rpw
----- Frank Kabel wrote: -----
Hi not possible without VBA
--

Regards
Frank Kabel
Frankfurt, Germany

 

[Dave Peterson]

I don't think you actually need the union method.

You can actually get multi-area ranges with stuff like:
set rng=wks.range("d131:d176,i131:i176")

And sometimes the code is pretty redundant--it looks pretty much the same except
for minor changes--worksheet name and range addresses for you.

Sometimes, you can put this kind of stuff in a loop. It usually makes it easier
to update later:

Option Explicit
Sub PrintDataWkShts2()

Dim myAddresses As Variant
Dim mySheetNames As Variant
Dim iCtr As Long
Dim wks As Worksheet

myAddresses = Array("d131:d176,i131:i176", "d17:d72", "P1778")

mySheetNames = Array("basic pricing", "Quick Price Sections", _
"Quick Price Accessories")

If UBound(myAddresses) <> UBound(mySheetNames) Then
MsgBox "Same number of addresses as sheets, please!"
Exit Sub
End If

For iCtr = LBound(mySheetNames) To UBound(mySheetNames)
Set wks = Nothing
On Error Resume Next
Set wks = Worksheets(mySheetNames(iCtr))
On Error GoTo 0
If wks Is Nothing Then
MsgBox "Take care typing those sheetnames--especially: " _
& mySheetNames(iCtr)
Exit Sub
End If
If Application.CountA(wks.Range(myAddresses(iCtr))) > 0 Then
wks.PrintOut preview:=True 'for testing!
End If
Next iCtr

'not sure why you have to select this stuff:

Sheets("Accessories Area (1)").Select
Range("C130:I190").Select
Sheets("Accessories Area (2)").Select
Range("C130:I190").Select
Sheets("Accessories Area (3)").Select
Range("C130:I190").Select
Sheets("Accessories Area (4)").Select
Range("C130:I190").Select
Sheets("Basic Pricing").Select
Range("D3").Activate

End Sub

Hi,

I've looked at the union function and it looks like it will do the trick. I haven't tried it yet, though. I did end up using multiple counta statements and adding each progressively to each fillcells statement. I did post a response that included an incomplete code that was working - I don't know if you had already seen that yet. But this Union function looks like i can shorten the length of the code.

Thanks for your input!

rpw

<<snipped>>

 

[rpw]

Hi Dave

Thanks for putting time and effort into this.

It looks like the sequence of myAddresses and mySheetNames must match so that the correct range is matched to the correct sheet

As I'm studying this further, I would like it if you verify a couple of things please? notes in line belo

----- Dave Peterson wrote: ----

I don't think you actually need the union method

You can actually get multi-area ranges with stuff like
set rng=wks.range("d131:d176,i131:i176"

'I guess I was mis-typing before 'cause that works now...

And sometimes the code is pretty redundant--it looks pretty much the same excep
for minor changes--worksheet name and range addresses for you

Sometimes, you can put this kind of stuff in a loop. It usually makes it easie
to update later

Option Explici
Sub PrintDataWkShts2(

Dim myAddresses As Varian
Dim mySheetNames As Varian
Dim iCtr As Lon
Dim wks As Workshee

myAddresses = Array("d131:d176,i131:i176", "d17:d72", "P1778"

mySheetNames = Array("basic pricing", "Quick Price Sections",
"Quick Price Accessories"

If UBound(myAddresses) <> UBound(mySheetNames) The
MsgBox "Same number of addresses as sheets, please!
Exit Su
End I

'UBound counts the number of entries in the array and this statement compares the two numbers. btw, nice error trap

For iCtr = LBound(mySheetNames) To UBound(mySheetNames

'with only 3 entries in the array, Lbound is 0 and ubound is 2. iCtr is then 3? (0,1,2). this limits the number of times the loop loops

Set wks = Nothin
On Error Resume Nex
Set wks = Worksheets(mySheetNames(iCtr)
On Error GoTo
If wks Is Nothing The
MsgBox "Take care typing those sheetnames--especially: "
& mySheetNames(iCtr
Exit Su
End I
If Application.CountA(wks.Range(myAddresses(iCtr))) > 0 The
wks.PrintOut preview:=True 'for testing
End I
Next iCt

'not sure why you have to select this stuff

'this was stuff that was left over from the first step building the macro originally. At the time of posting I had not completed going through and converting the 'range.select' into counta statements. A lot of redundant steps changing only the sheet names and ranges. Now, my next step is to include these sheet names and ranges into the arrays above and delete the previous stuff.

Sheets("Accessories Area (1)").Selec
Range("C130:I190").Selec
Sheets("Accessories Area (2)").Selec
Range("C130:I190").Selec
Sheets("Accessories Area (3)").Selec
Range("C130:I190").Selec
Sheets("Accessories Area (4)").Selec
Range("C130:I190").Selec
Sheets("Basic Pricing").Selec
Range("D3").Activat

Thanks again for all of your help, this is a much better/cleaner solution

rp
<<snipped>

 

[Dave Peterson]

responses also interspersed.

Hi Dave,

Thanks for putting time and effort into this.

It looks like the sequence of myAddresses and mySheetNames must match so that the correct range is matched to the correct sheet.

You betcha. Else all heck will break loose!

As I'm studying this further, I would like it if you verify a couple of things please? notes in line below

----- Dave Peterson wrote: -----

I don't think you actually need the union method.

You can actually get multi-area ranges with stuff like:
set rng=wks.range("d131:d176,i131:i176")

'I guess I was mis-typing before 'cause that works now....

And sometimes the code is pretty redundant--it looks pretty much the same except
for minor changes--worksheet name and range addresses for you.

Sometimes, you can put this kind of stuff in a loop. It usually makes it easier
to update later:

Option Explicit
Sub PrintDataWkShts2()

Dim myAddresses As Variant
Dim mySheetNames As Variant
Dim iCtr As Long
Dim wks As Worksheet

myAddresses = Array("d131:d176,i131:i176", "d17:d72", "P1778")

mySheetNames = Array("basic pricing", "Quick Price Sections", _
"Quick Price Accessories")

If UBound(myAddresses) <> UBound(mySheetNames) Then
MsgBox "Same number of addresses as sheets, please!"
Exit Sub
End If

'UBound counts the number of entries in the array and this statement compares the two numbers. btw, nice error trap.

And since the lower bound will always be the same, I don't need to include a
check for that.

For iCtr = LBound(mySheetNames) To UBound(mySheetNames)

'with only 3 entries in the array, Lbound is 0 and ubound is 2. iCtr is then 3? (0,1,2). this limits the number of times the loop loops?

iCtr will vary from 0 to 2 while it does the important work.

But watch out. Try this little routine:

Option Explicit
Sub testme()
Dim iCtr As Long
For iCtr = 1 To 3
MsgBox iCtr
Next iCtr
MsgBox iCtr
End Sub

So you'll want to be careful if you're going to use that variable later.

And it doesn't actually have to start with 0. If you add a line at the top of
your procedure, right next to "Option Explicit", you can change the base for
these arrays to 1 (1,2,3) (Use "Option Base 1"). But with "option base 0" (or
not used), then the lbound will start at 0 (0,1,2).

But because I'm starting with the lbound and going up to the ubound, I don't
really care.

And you could define an array like:

dim myArray(48 to 52) as string

And it's lower bound will be 48.

Set wks = Nothing
On Error Resume Next
Set wks = Worksheets(mySheetNames(iCtr))
On Error GoTo 0
If wks Is Nothing Then
MsgBox "Take care typing those sheetnames--especially: " _
& mySheetNames(iCtr)
Exit Sub
End If
If Application.CountA(wks.Range(myAddresses(iCtr))) > 0 Then
wks.PrintOut preview:=True 'for testing!
End If
Next iCtr

'not sure why you have to select this stuff:

'this was stuff that was left over from the first step building the macro originally. At the time of posting I had not completed going through and converting the 'range.select' into counta statements. A lot of redundant steps changing only the sheet names and ranges. Now, my next step is to include these sheet names and ranges into the arrays above and delete the previous stuff.

No one who likes the taste of sausage should watch it being made. No one who
respects the law should watch the lawmakers in action. (Same thing applies to

 

[Dave Peterson]

Actually, I think it does "overcount" in both situations.

Add a msgbox to your printdatawkshts2 routine right after it falls out of the
For/Next loop.

for ictr = 1 to 3
msgbox ictr
next ictr

ictr starts at 1, does a msgbox.
Increments to 2 and checks the "For" limits (1 to 3)

still ok? Yep?
msgbox ictr '2 this time
increments to 3 and checks "For" once again.

still ok? Yep.
msgbox ictr '3 this time
increments to 4 and checks the "For" and fails!
skips to next statement.

I think you never saw this, because you never used that variable again. (I
think it would be pretty weird to use that ictr variable's value after a for
next/loop.

But I have done things like:

ictr = 0
do
if something is false then
ictr = ictr + 1
else
exit do
end if
loop

So I could use that counter variable's value.

Take a look at all the Looping constructs in VBA's help--Do/while, do/until, for
each/next, for/next. There's lots of ways to attack the same problem.

And yep. iCtr is my standard name for a generic counter. "i" used to indicate
integer, but now it means Long <bg>. (I don't like lCtr--I have trouble
distinguishing l's from 1's (ELL's from One's)

===

 

[rpw]

Yeah, you're right - it was over-counting in both loops, it's just that in the testme loop had two message box statements. It was the second one that ran after the loop 'failed' that ran the message box and printed "4". In the other loop there was not a matching worksheet to print and I think that's why I didn't 'see' it over count

Thank you again for taking the time to type in the explanations. I really appreciate it. Helps me to understand a little more each time I ask for help

rp


----- Dave Peterson wrote: ----

Actually, I think it does "overcount" in both situations

Add a msgbox to your printdatawkshts2 routine right after it falls out of th
For/Next loop

for ictr = 1 to
msgbox ict
next ict

ictr starts at 1, does a msgbox.
Increments to 2 and checks the "For" limits (1 to 3

still ok? Yep
msgbox ictr '2 this tim
increments to 3 and checks "For" once again

still ok? Yep
msgbox ictr '3 this tim
increments to 4 and checks the "For" and fails
skips to next statement

I think you never saw this, because you never used that variable again. (
think it would be pretty weird to use that ictr variable's value after a fo
next/loop

But I have done things like

ictr =
do
if something is false the
ictr = ictr +
els
exit d
end if
loo

So I could use that counter variable's value

Take a look at all the Looping constructs in VBA's help--Do/while, do/until, fo
each/next, for/next. There's lots of ways to attack the same problem

And yep. iCtr is my standard name for a generic counter. "i" used to indicat
integer, but now it means Long <bg>. (I don't like lCtr--I have troubl
distinguishing l's from 1's (ELL's from One's

==
On the other hand, sometime iRow will mean InputRow and oRow will mea
OutputRow, too



rpw wrote

 

[Dave Peterson]

I find starting a new workbook and putting some simplified code and trying it
out works wonders.

You can learn lots by blowing things up real good!

Yeah, you're right - it was over-counting in both loops, it's just that in the testme loop had two message box statements. It was the second one that ran after the loop 'failed' that ran the message box and printed "4". In the other loop there was not a matching worksheet to print and I think that's why I didn't 'see' it over count.

Thank you again for taking the time to type in the explanations. I really appreciate it. Helps me to understand a little more each time I ask for help.

rpw

<<snipped>>