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

    adjust cicle for sheet (2000 sr 1)

    ... for a test i have maked this macro but work to condider foglio1, foglio2 and foglo3...( i have used a cicle for next ) now in effect the real sheet are named SALDI, MENUand RAPPORTI HOW CAN TO MOFIFY THIS ?
    tKS.
    Sub FILTRA_E_COPIA()

    Dim Uniques() As Variant
    Dim un As Variant
    Dim i As Long
    Dim DEST As Worksheet

    Application.ScreenUpdating = False

    Range([A2], [A2].End(xlDown)).AdvancedFilter Action:=xlFilterCopy, Unique:=True, CopyToRange:=[Z1]
    Uniques = Range([Z2], [Z2].End(xlDown)).Value
    Columns("Z").ClearContents

    For Each un In Uniques
    For i = 1 To 3
    Set DEST = Worksheets("Foglio" & i)
    Range("A2").AutoFilter Field:=1, Criteria1:=un
    Range([A2:G2], [A2:G2].End(xlDown)).Copy DEST.Range("A2")
    Range("A2").AutoFilter
    Next
    'Call mymacro

    For i = 1 To 3 'The loop to remove the data from the 3 Foglio sheets
    Set DEST = Worksheets("Foglio" & i)
    DEST.Range("A3:G65536").ClearContents
    Next
    Next

    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: adjust cicle for sheet (2000 sr 1)

    Instead of
    <code>
    For i = 1 To 3
    Set DEST = Worksheets("Foglio" & i)
    ...
    Next i
    </code>
    use
    <code>
    For Each DEST In Worksheets(Array("SALDI", "MENU", "RAPPORTI"))
    ...
    Next DEST</code>

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

    Re: adjust cicle for sheet (2000 sr 1)

    good...
    Tks.

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

    Re: adjust cicle for sheet (2000 sr 1)

    solved TKS, Dont worry!
    ... Sorry me Hans,
    but is possible to limit the copy of range, only for sheets MENU and RAPPORTI the column A:E ...

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

    Re: adjust cicle for sheet (2000 sr 1)

    Either split the code into two: one piece of code for MENU and RAPPORTI, and another piece of code for SALDI, or build in an extra If ... Then ... End If.

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

    Re: adjust cicle for sheet (2000 sr 1)

    Very important your comment...
    If you have other sugestion and a "professional" code are, naturally welcome!
    In effect after each copy of range filtred i would want run MyMacro, after MyMacro finished go to the next "block" of filtred record....
    Wath you think about my code? And it work correct, for you?

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

    Re: adjust cicle for sheet (2000 sr 1)

    You could make the code a little bit more efficient by setting the AutoFilter once for each element of UNIQUES, instead of setting it each time inside the For I = ... loop:

    For Each UN In UNIQUES
    Range("A2").AutoFilter Field:=1, Criteria1:=UN
    For I = LBound(DESTS) To UBound(DESTS)
    Set DEST = Worksheets(DESTS(I))
    If DESTS(I) = "SALDI" Then
    Range(<!t>[A3:G3]<!/t>, <!t>[A3:G3]<!/t>.End(xlDown)).Copy DEST.Range("A3")
    Else
    Range(<!t>[A3:F3]<!/t>, <!t>[A3:F3]<!/t>.End(xlDown)).Copy DEST.Range("A3")
    End If
    Next
    Range("A2").AutoFilter
    ...

    I don't know what you want to accomplish with the code, so I cannot judge if it works correctly. It runs without error.

Posting Permissions

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