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

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

0
anonymous (74722)
5/26/2004 8:21:04 PM
excel.misc 78881 articles. 5 followers. Follow

13 Replies
717 Views

Similar Articles

[PageSpeed] 19

Hi
not possible without VBA

--
Regards
Frank Kabel
Frankfurt, Germany

"rpw" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag
news:732719CD-1559-4CD1-B0BF-F677853048F9@microsoft.com...
> 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'?
>
>
> TIA
>
> rpw
>

0
frank.kabel (11126)
5/26/2004 8:32:30 PM
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
    
     "rpw" <anonymous@discussions.microsoft.com> schrieb im Newsbeitra
     news:732719CD-1559-4CD1-B0BF-F677853048F9@microsoft.com..
     > Hi everyone
     >> I have a workbook that has 7 worksheets un-hidden.  Of those, eac
     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'
     >>> TI
     >> rp
     >
0
anonymous (74722)
5/26/2004 8:56:03 PM
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("D131:D176").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("P17:P78").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
          
          "rpw" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag
          news:732719CD-1559-4CD1-B0BF-F677853048F9@microsoft.com...
          > 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'?
          >>> TIA
          >> rpw
          >
0
anonymous (74722)
5/26/2004 9:51:03 PM
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

"rpw" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag
news:A11F1019-7725-4B1B-9EE9-B70938AA9AD9@microsoft.com...
> 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("D131:D176").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("P17:P78").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
>
>           "rpw" <anonymous@discussions.microsoft.com> schrieb im
Newsbeitrag
>           news:732719CD-1559-4CD1-B0BF-F677853048F9@microsoft.com...
>           > 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'?
>           >>> TIA
>           >> rpw
>           >

0
frank.kabel (11126)
5/26/2004 9:57:42 PM
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"), ("D1:D70"
               ("A1:A70", "D1:D70"
               ("A1:A70" + "D1:D70"
               ("A1:A70") +("D1:D70"
               ("A1:A70, D1:D70"
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
    
     "rpw" <anonymous@discussions.microsoft.com> schrieb im Newsbeitra
     news:A11F1019-7725-4B1B-9EE9-B70938AA9AD9@microsoft.com..
     > 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
     >> Here's what's in the macro so far...
     >> Option Explici
     >> Sub PrintDataWkShts(
     > 
     > ' PrintDataWkShts Macr
     > ' Macro recorded 5/26/2004 by rp
     > 
     > ' Keyboard Shortcut: Ctrl+
     > 
     >     Sheets("Basic Pricing").Selec
     >     Range("D131:D176").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("P17:P78").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
     >>      ----- rpw wrote: ----
     >>      Hi Frank
     >>      Thanks for the quick response.  I'm not very skilled at VBA.  
     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
     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 
     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" <anonymous@discussions.microsoft.com> schrieb i
     Newsbeitrag
     >           news:732719CD-1559-4CD1-B0BF-F677853048F9@microsoft.com...
     >> 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'?
     >>>> TIA
     >>> rpw
     >>
0
anonymous (74722)
5/26/2004 11:06:01 PM
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("D131:D176"
    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("D17:D72"
    fillcells = WorksheetFunction.CountA(rng
    If fillcells > 0 The
        ActiveSheet.PrintOu
    End I
    fillcells = 
    Sheets("Quick Price Accessories").Selec
    Set rng = ActiveSheet.Range("P17:P78"
    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"), ("D1:D70"
                    ("A1:A70", "D1:D70"
                    ("A1:A70" + "D1:D70"
                    ("A1:A70") +("D1:D70"
                    ("A1:A70, D1:D70"
     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
         
          "rpw" <anonymous@discussions.microsoft.com> schrieb im Newsbeitra
          news:A11F1019-7725-4B1B-9EE9-B70938AA9AD9@microsoft.com..
          > 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
          >> Here's what's in the macro so far...
          >> Option Explici
          >> Sub PrintDataWkShts(
          > 
          > ' PrintDataWkShts Macr
          > ' Macro recorded 5/26/2004 by rp
          > 
          > ' Keyboard Shortcut: Ctrl+
          > 
          >     Sheets("Basic Pricing").Selec
          >     Range("D131:D176").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("P17:P78").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
          >>           "rpw" <anonymous@discussions.microsoft.com> schrieb im
          Newsbeitrag
          >           news:732719CD-1559-4CD1-B0BF-F677853048F9@microsoft.com...
          >> 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'?
          >>>> TIA
          >>> rpw
          >>
0
anonymous (74722)
5/26/2004 11:21:01 PM
Hi
have a look at the Union method or use multiple counta statements and
add them together

--
Regards
Frank Kabel
Frankfurt, Germany

"rpw" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag
news:9C1F6D8D-0A3E-4381-89D5-A1CCDC27E05A@microsoft.com...
> 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"), ("D1:D70")
>                ("A1:A70", "D1:D70")
>                ("A1:A70" + "D1:D70")
>                ("A1:A70") +("D1:D70")
>                ("A1:A70, D1:D70")
> 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
>
>      "rpw" <anonymous@discussions.microsoft.com> schrieb im
Newsbeitrag
>      news:A11F1019-7725-4B1B-9EE9-B70938AA9AD9@microsoft.com...
>      > 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("D131:D176").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("P17:P78").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
>      >>           "rpw" <anonymous@discussions.microsoft.com> schrieb
im
>      Newsbeitrag
>      >
news:732719CD-1559-4CD1-B0BF-F677853048F9@microsoft.com...
>      >> 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'?
>      >>>> TIA
>      >>> rpw
>      >>

0
frank.kabel (11126)
5/27/2004 6:07:48 AM
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", "P17:p78")

    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
	

rpw wrote:
> 
> 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>>
-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
5/27/2004 11:04:25 PM
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", "P17:p78"
    
         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 Peterso
     ec35720@msn.co
     
0
anonymous (74722)
5/28/2004 6:06:05 PM
responses also interspersed.

rpw wrote:
> 
> 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", "P17:p78")
> 
>          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
code! <vbg>)


> 
>          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
> 
> Thanks again for all of your help, this is a much better/cleaner solution.
> 
> rpw
>       <<snipped>>
>      --
> 
>      Dave Peterson
>      ec35720@msn.com
> 

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
5/28/2004 11:05:47 PM
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)

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



rpw wrote:
> 
> Hi Dave,
> 
> OK, so now I'm perplexed with why your "testme" over-counts by 1 on the iCtr but the "PrintDataWkShts2" doesn't.  Is that because the wks variable couldn't find a matching worksheet thereby causing an error?  If that's the case, then "On Error Goto 0" does what exactly?  Is "0" a line number?  Why not "Exit Sub"?
> 
> (Sorry for all the questions, but VBA is still very confusing to me.)
> 
> Thanks again for putting the time into this - I really appreciate the help and the education.
> 
> Oh, another question...  iCtr is your own abbreviation for "integerCounter"?
> 
> rpw
> 
>      ----- Dave Peterson wrote: -----
> 
>      responses also interspersed.
> 
>      rpw wrote:
>      >> 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", "P17:p78")
>      >>          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
>      code! <vbg>)
> 
> 
>      >>          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
>      >> Thanks again for all of your help, this is a much better/cleaner solution.
>      >> rpw
>      ><<snipped>>>      --
>      >>      Dave Peterson
>      >      ec35720@msn.com
>      >
> 
>      --
> 
>      Dave Peterson
>      ec35720@msn.com
> 

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
6/1/2004 11:28:35 PM
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
     >> Hi Dave
     >> OK, so now I'm perplexed with why your "testme" over-counts by 1 on the iCtr but the "PrintDataWkShts2" doesn't.  Is that because the wks variable couldn't find a matching worksheet thereby causing an error?  If that's the case, then "On Error Goto 0" does what exactly?  Is "0" a line number?  Why not "Exit Sub"
     >> (Sorry for all the questions, but VBA is still very confusing to me.
     >> Thanks again for putting the time into this - I really appreciate the help and the education
     >> Oh, another question...  iCtr is your own abbreviation for "integerCounter"
     >> rp
     >>      ----- Dave Peterson wrote: ----
     >>      responses also interspersed
     >>      rpw wrote
     >>> 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 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", "P17:p78"
     >>>          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
     >      code! <vbg>)
     >>>>>          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
     >>> Thanks again for all of your help, this is a much better/cleaner solution.
     >>> rpw
     >><<snipped>>>      --
     >>>      Dave Peterson
     >>      ec35720@msn.com
     >>>>      --
     >>      Dave Peterson
     >      ec35720@msn.com
     > 
     
     -- 
     
     Dave Peterson
     ec35720@msn.com
     
0
anonymous (74722)
6/2/2004 12:36:02 AM
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!



rpw wrote:
> 
> 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>>

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
6/2/2004 12:40:31 AM
Reply:

Similar Artilces:

Problems migrating BCM data into CRM SB edition
Hi There I am having a problem migrating data from Business Contacts Manager (BCM) into CRM 3.0 Small Business edition. I have downloaded the BCM data migration pack and have followed the data migration documentation to the letter. I even cleaned up the BCM database prior to copying the files, checking them for errors using the Manage Database option in the Business Tools menu. It gets so far through the migration process and then bombs out. Here is the final few entries from the log file: 28/10/2006 12:18:53------>Transitioning to next screen. From: ConfigurationSummary screen. To: ...

Printing double sided brochures in publisher
I am trying to print a tri-fold double sided brochure in Publisher - my problem is that the 2nd page prints upside down - how do you get both sides of the brochure to be printed the same direction? REDLED wrote: > I am trying to print a tri-fold double sided brochure in Publisher - my > problem is that the 2nd page prints upside down - how do you get both sides > of the brochure to be printed the same direction? If you're manually duplexing: Rotate the second page through 180° before reinserting it into the printer. If you're auto-duplexing: Change the options on ...

transfer data from multiple columns to singlr column
I have data in form a d g b e h c f i (but larger scale) and I need it in a single column going a to z. Hi, highlight you data, copy, go to the column where you want to see the data, paste special, transpose "lc85" wrote: > I have data in form a d g > b e h > c f i (but larger scale) > and I need it in a single column going a to z. You up for using a macro? Sub ToOneColumn() 'dantuck Mar 7, 2007 &...

double clicking and draging a column in a chart to chg data
in Excel 2003, double clicking on a column in a chart and then dragging the column up or down would change the data in a table upon which the chart depended. How does one do this in excel 2007? Tom Hi, That feature has been removed in 2007, there is no way to do it. If this helps, click the Yes button. -- Thanks, Shane Devenshire "Tom of inns" wrote: > in Excel 2003, double clicking on a column in a chart and then dragging the > column up or down would change the data in a table upon which the chart > depended. > > How does one do this in excel 2007? &g...

incorrect check / cheque number printing
When generating a cheque run, let’s say cheque 61, 62, 63 and 64, the information printed on cheque # 62 is actually cheque # 64 and vice versa. This does not happen every cheque run, but it does happen fairly often. I have not seen GP do this. What version and service pack of GP are you using? Are you 100% sure the check stock itself is not out of sequence? Do you have any customizations? -- Victoria Yudin Dynamics GP MVP Flexible Solutions, Inc. "Syed" <Syed@discussions.microsoft.com> wrote in message news:3B02ECEF-7436-4CF3-930E-AA4D2DFDF117@microsoft.com... ...

Removing multiple users from Outlook
We did some computer switching, which involved uninstalling a copy of Office XP and replacing it with a copy of Office 2003. Now when I open Outlook, it makes me choose a user. This wasn't in the .pst file which was transferred for the other computer, but apparenly it was on this computer someplace. How can I get Outlook to simply open for the one account without that dialog box popping up? Aloha Annie, Control Panel | Mail | Show Profiles...remove any profiles you don't want. Select the one you do want and set it as the default. (if there are more than one left) -Ben-...

Can't open 2005 data file after reinstalling Money 2005
I am experiencing a recurring problem. I have had to reinstall Windows XP and MS Money 2005. I am now unable to open my previously converted 2005 file or restore any backup version. I consistently get the following error message: "Money cannot locate filename or cannot open it, possibly because it is a read-only file, you do not have permission to change it, or your disk drive is write- protected. If you have chosen the correct file and it cannot be accessed, you will need to click OK and then Restore your most recent backup file." Any help or thoughts would be greatly ap...

printing 2 charts on single page
Using Excel 2k, Win 2k I was trying to print to line charts on a single page to demonstrate the corelation between the two data sets. I would like to print in portait mode with one chart on top of page and one on bottom. Excel only seems to offer putting chart 2 on a separate page or embedding it in chart one. I am not very familiar with excel charting capabilites can anyone suggest a resorce? Thanks ...

Comments to print on invoices and packing lists
We have a client with a specific need for printing customer comments and item comments: 1) They want to store comments on Customer Master. When a sales order is entered these comments should default on the sales order with an option to change. They want to mark these comments to be printed on sales order, invoice, packing list and pick ticket. 2) Items are stored with item specific comments. These comments need to come to the sales order when the order is entered with an option to change. They want to setup comments with similar setup where they print on order, invoice, pick ticket and packi...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

printing multiple copies of Sigel GmbH
I have made a tent card in a small size of 5.588cm x 4.191cm for a swing tag, using Sigel GmbH in the "other labels" section. It prints one copy perfectly, leaving room on the page for at least about another 5 copies (which is what I want), but when in the print page section it doesn't give me the option to print multiple copies on the page. This will be an enormous waste of paper if I have to print the number of copies individually on each page. HELP! -- candere What is the product number for this label? I cannot find this size label on my 2007 version. -- Mary Sau...

Customer check data
When customers pay by check RMS asks for specific information such as drivers license number, routing number, account number, address and phone number. Does anyone have a report and or a way to extract this info from the database for cases when the check is returned for NSF? Please advise, Scott We can write you this report. Contact me for detail on price . Afshin Alikhani - [ afshin@retailrealm.co.uk ] CEO - Retail Realm = = = = = = = = = = "Scott Santorio" <scott@tt-newyork.com> wrote in message news:e8ZKkR6$HHA.3716@TK2MSFTNGP03.phx.gbl... > When customers pa...

Macro to seperate data
Hi I seem to be struggling to find a macro that will work in previous threads. In sheet 1 is a list of data in columns A:N and the number of rows will vary. It is a list of sales with each sale record ocuppying one row. The salesperson's name is in column C and each salesperson will have multiple entries. What I am trying to do is create a seperate summary sheet in the workbook for each salesperson. Therefore sheets 2 to 20 are templates that already exist with a different salesperson's name entered into cell C3 on each of them. I am trying to find a macro that ...

Need HELP! for Linking data
Could someone please direct me to where I can learn how to link date in a work book. i.e., I have individual pages for each subject but I need the data that is entered in these individual pages to transfer to the Master page without having to manually in put it.........TNX Bubey, There are not too many bits about linking worksheets or workbooks that I can find. But have a look at the links below, in case they give you the information you need. I think it is frustratingly one of those things which is very easy when you know how, or if you can get someone to actually show you, but if you hav...

printing imported gif files in Excel
When printing gif file with transparent background on the grey(25%) background of Excel non-transparent part of the picture has white border around it. Is there any way to prevent it? Thank you in advance... Tim ...

Adding a combo box to a worksheet
Hi all, I'm re-creating one of our paper forms in Excel and I'd like to add combo boxes to some blanks on the form to allow the user to choose a name from a list. I know a little about Excel formulas and no VB code at all...what's the idiot-proof way to do this? Thanks, Chris Hi Chris, The easiest way is to right-click within Excel in the toolbars area and select the "Forms" toolbar. Then Forms toolbar should then appear and could can select the "Combo Box" icon and click on that. If you can't tell which icon represents the Combo Box, just hover yo...

VBA to put a copy of worksheet on the desktop 05-13-10
Hi all, In my workbook XYZ I have a sheet ABC. With a button on sheet DEF I can refresh sheet ABC. When the code finishes it job I want to add the actual date (short European notation dmyy) and time (f.i. 241110 16.31) to the name of the sheet (which becomes ABC 241110 16.31) and after that make a copy of that sheet in a separate workbook and put that workbook as an icon on the desktop of my computer. Is this possible? If so, please help me with the necessary code. Thanks in advance for your assistance. Jack Sons The Netherlands ...

Macro
I need a macro that help me to transfer name and address information from an specific table in excel to a template in words on specific areas and then print the word document. The reason for this is that i need to create diferents letters to be sent to the customers from the excel table. Example of the table is: soc seg, customer name, child name, customer code, add 1 , add2, city, estate, zip code. all this information will be paste on word letter template on specific areas or fields. Any suggestion!!! -- nicoro Hi IMHO the best approach would be to set up a mail merge documen...

Indirect Method for Name Box Variable? #2
DARN The reason I want to do this is that I have to collect data on a dail basis but I have to display it on a graph on a weekly basis. I select the entire week of cells and give it a name like week52. The following week I have to select the entire week and name i week53. Everything is set up that I can just copy and the numbers will progres for whatever length of time I need to graph (months, years, etc) excep this. Maybe I'll just go back to using the range of cell numbers. Thanks guys -- Chris Brenna ----------------------------------------------------------------------- Chris Bre...

macros entering data
How do I create a macro that goes to one cell then waits until I enter new data, then goes to another cell and waits until I enter new data etc? thanks How about something like sub Enter_Data() dim NewValue NewValue = inputbox("Enter the value for cell A1: ") range("a1").value = NewValue NewValue = inputbox("Enter the value for cell G2: ") range("g2").value = NewValue NewValue = inputbox("Enter the value for cell I8: ") range("i8").value = NewValue end sub ...

Cell with large amount of data not showing all data
I'm running Excel 97. I have a cell with 358 words (1928 characters with spaces). Word wrap is on for the cell. Only part of the text is displayed even though the cell is big enough to show everything. If I make the cell wider (wider than a page) more of the text shows but not everything. I tried a new worksheet with the same text and had the same problem. Is this a known issue with excel? Is there a solution? Thanks, Brad Left to its own devices, excel will only show about 1000 characters in a cell. But you can add some alt-enters (to force a new line within the cell) and see more s...

unable to grant existing user access to TEST company
Set up test company using live company data; ran the script referenced in CustomerSource article ID#871973; can grant and remove user access in all other companies; ran grantsql 9.2; the dexsql log shows the user already has access probably because of using the other company data for the test; I can set up a new user and grant them access, existing users who already have access can view the company...but, I cannot figure out how to grant access to the TEST company to an existing user. The error popup reads "The user could not be added to one or more databases." Dynamics 9.0,...

Sorting by file extension in a worksheet
Hello, I have an excel worksheet which has a list of file names from a directory and various stats about each file. I need to be able to sort them by the file extension. Eg. c:\documents\folderA\picture1.eps c:\documents\folderB\document.doc I would like to sort them by the ".eps" extension. I have tried using the Data,Sort menu and something like *?*.eps as the criteria. But am obvisouly missing something. Any help would be most appreciated. Thanks Karl You could use a helper column to extract just the extension then include this in your sort range and sort on the helper col...

How export all mailbox-enabled users from the GAL using Outlook?
Hello, We have a user which, for administrative reasons, needs to export all mailbox-enabled users in the GAL now and then. They just need all the names. Is there a straightforward way for them to do that? We don't want to give them any special permissions and want to avoid server scripting. Ideally, they should be able to export it to a CSV file or any text file. Thanks, - Alan. Alan wrote: > We have a user which, for administrative reasons, needs to export all > mailbox-enabled users in the GAL now and then. They just need all the > names. Is there a straightforward way fo...

TSP100 printer and print width
Hi all, After pull my hair out for a half of day, I just find out that TSP100 printer driver reports its print width incorrectly when we use GetDeviceCaps to retrieve print width. Call GetDeviceCaps with PHYSICALWIDTH will report 1725 that is roughtly equal to 8.5 inches corresponding to 203ppi that LOGPIXELSX reported. Just let you all know This is a multi-part message in MIME format. ------=_NextPart_000_0048_01C92EF4.F767C240 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable After you installed the printer, did you change the p...