Page 1 of 3 123 LastLast
Results 1 to 15 of 36
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    LOOP sheets (2000 sr 1)

    this is my old code to ciclyng a sheet and call MyMacro...
    Actually the format of sheet not is numeric but have this format:

    4543-018769
    4543-008356
    ...

    how can to modify the macro?

    code:
    Sub LOOPSHEET()
    Application.ScreenUpdating = False

    Dim i As Long
    For i = 1 To Sheets.Count
    If IsNumeric(Sheets(i).Name) Then
    Sheets(i).Select
    Call ESTRATTI_CICSEA
    End If
    Next 'i
    Worksheets("SALDI").Select
    Application.ScreenUpdating = True
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: LOOP sheets (2000 sr 1)

    Do all these sheets have a dash "-" as the fifth character in their name?
    Do you have other sheets with a dash as the fifth character that should NOT be included (for example "Roma-Napoli")?

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LOOP sheets (2000 sr 1)

    Hans first i am very happy to see you on my post... only you are <img src=/S/artist.gif border=0 alt=artist width=34 height=29>
    Yes, all my sheet in question have the dash "-".
    Other sheet have the name: MENU, PROVA, SALDI Eeccc...

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: LOOP sheets (2000 sr 1)

    Come on, Sal, you have received excellent help from others too!

    You could replace the line

    If IsNumeric(Sheets(i).Name) Then

    with

    If Mid(Sheets(i).Name, 5, 1) = "-" Then

    This tests if the 5th character is a "-".

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LOOP sheets (2000 sr 1)

    ... is possible to assign into a button a macro that print ,on default printer, all sheet whit this format:

    4543-018769
    4543-008356
    ...

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: LOOP sheets (2000 sr 1)

    You know now, from this thread or from this newsgroup thread how to loop through these sheets. You also know how to print a sheet. So it shouldn't be hard to create the necessary code.

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LOOP sheets (2000 sr 1)

    .... I used this macro for print all sheet...
    but is possible to not show the tipical windows of printer status when the sheet is printed...?
    In effect the windows staus printer apper and disapper for 34 sheets during the job...

    Sub LOOPSHEET_PRINT()
    Application.ScreenUpdating = False

    Dim i As Long
    For i = 1 To Sheets.Count
    If Mid(Sheets(i).Name, 5, 1) = "-" Then

    Sheets(i).Activate
    Sheets(i).Visible = xlSheetVisible
    Sheets(i).Select
    ActiveWindow.SelectedSheets.PrintOut
    Sheets(i).Visible = False
    End If

    Next

    Application.ScreenUpdating = True
    Worksheets("SALDI").Select
    End Sub

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LOOP sheets (2000 sr 1)

    No, you can not prevent that message box from displaying.
    Legare Coleman

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: LOOP sheets (2000 sr 1)

    This is not an Excel question. See Disabling Printing Notification Dialog Boxes in Windows if you're using Windows 2000, or How To Enable or Disable Print Job Notifications in Windows XP if you're using Windows XP.

  10. #10
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LOOP sheets (2000 sr 1)

    I Hans, see wath i have found, but ure not rme to integrate thi code into my macro...

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: LOOP sheets (2000 sr 1)

    Didn't my previous reply or this help?

  12. #12
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LOOP sheets (2000 sr 1)

    hi Hans,
    In this attached file are present various sheet named:

    4543-018769
    4543-008356
    ...
    Note: attached i have insert a userform.zip used in my project for other activity....
    in each sheet are present into B5 a code based similar TABLE_CODE:

    00 SALDO DEBITORE IRRECUPERABILE
    01 RAPPORTI TITOLI COLLEGATI
    02 CONTO ESTINTO
    03 ERRATA RICHIESTA CHIUSURA
    04 FIDO IN ESSERE
    05 SUCCESSIONE
    06 IN ATTESA REGOLAMENTO PARTITE VARIE
    99 NON COMUNICATO

    is possible to insert into a button a command/macro to show a userform to show this TABLE_CODE and when i select one of this code print all sheet with the code into B5...

    Example:

    I click on

    03 ERRATA RICHIESTA CHIUSURA

    All sheet with this code ("03") are printed...

    Here is the code i use to print all sheets without selection on code...:

    Sub LOOPSHEET_PRINT()
    Application.ScreenUpdating = False

    Dim i As Long
    For i = 1 To Sheets.Count
    If Mid(Sheets(i).Name, 5, 1) = "-" Then

    Sheets(i).Activate
    Sheets(i).Visible = xlSheetVisible
    Sheets(i).Select
    ActiveWindow.SelectedSheets.PrintOut
    Sheets(i).Visible = False
    End If

    Next

    Application.ScreenUpdating = True

    Worksheets("SALDI").Select
    End Sub

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: LOOP sheets (2000 sr 1)

    See attached version. I adapted the userform for your purposes.

  14. #14
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LOOP sheets (2000 sr 1)

    wow!!!!!!!!!!!!!
    Only a little modify...
    Is possible to show a msg box "NESSUNO FOGLIO CON CODICE "00" if i select from userform the option "00" and into range B5 of all sheets not is present the code "00"?

    ... Insert into userform a new line with the possibility to print all... for example "STAMPA TUTTO"

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: LOOP sheets (2000 sr 1)

    You should be able to add code for that yourself:
    - Define a variable lngCount.
    - Each time a sheet is printed, increase lngCount by 1.
    - If lngCount is still 0 at the end, the value was not found, so display a message box.

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •