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

    FILTRE AND PAST IN WORD (2000 sr 1)

    Hi, my problem is:
    From this sheet filtre from the column a "SPORT" cut the related line and put in a Word doc
    Example:
    filtre 4500 NAPOLI copy all selected line and paste in word doc
    filtre 4501 NAPOLI AG.1 copy all selected line and paste in word doc
    ecc...
    Before this opeartion make another filtre on the column "E" S.DO CONT. and select only a line under -100,00

    example from -00,00 to -100,00

    here is the sheet:

    http:www.gssitaly.comover_milion.zip

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

    Re: FILTRE AND PAST IN WORD (2000 sr 1)

    Do you want to specify the filter yourself, or do you want the macro to filter each value in turn? If the latter, should each value be copied to a new document, or should all values be copied into the same document?

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

    Re: FILTRE AND PAST IN WORD (2000 sr 1)

    sorry Hans but i not have consider that...
    tK Hans,

    For this:
    - Do you want to specify the filter yourself, or do you want the macro to filter each value in turn?
    Make a simple button on the sheet a start automatiocly for every recod filtred...
    During this operatin save in automaticly every doc of Word in e:docpasted with same name of the first record filtred...
    Example:
    4500_NAPOLI.DOC
    4501_AGENZIA_1.DOC

    another add in:

    is possble to insert in the bootom of the doc the nr. of position iltred in the last line of the record filtred for example:

    POSITION EXATACTED NR. 51

    And insert for every list the total of amount always in the botom of column S.DO CONT.:

    example:

    AMOUNT OF POSITION EXATACTED EURO -458,44

    naturally for each doc genareted insert in the top the head of column of the sheet...

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

    Re: FILTRE AND PAST IN WORD (2000 sr 1)

    I think you should hire a professional programmer to do this. You're asking for a complete solution again, instead of a specific technical question.

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

    Re: FILTRE AND PAST IN WORD (2000 sr 1)

    ok! help me only a sample filtred record and pu in doc for every extraction....

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

    Re: FILTRE AND PAST IN WORD (2000 sr 1)

    To copy a filtered set:

    Range("A1").CurrentRegion.SpecialCells(xlCellTypeV isible).Copy

    You can use Automation to control Word in code from Excel. Moderator <!profile=WendellB>WendellB<!/profile> has a short tutorial on Automation on his website (see his profile), with links to useful information. You can also search for Word.Application in this forum or in the VBA forum.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: FILTRE AND PAST IN WORD (2000 sr 1)

    Does this work?
    It adds a filter (if needed), shows all the data, filters from -100 to 0.
    It then copies the (visible) values from col A to a temp sheet where it uses adv filter to get the unique names
    It then filters the list on each unique name and copies the data to new word document.

    Afterwards you will several word documents in a new word application

    Steve

    <pre>Option Explicit
    Sub CodeForSAL()
    Dim rCell As Range
    Dim rng As Range
    Dim rTemp As Range
    Dim wksS As Worksheet
    Dim wksTemp As Worksheet
    Dim wdApp As Object

    Application.ScreenUpdating = False
    Set wksS = Worksheets("20041103153514")
    Set wksTemp = Worksheets.Add
    Set wdApp = CreateObject("Word.Application")
    wdApp.Visible = True

    'Turn on Autofilter if not on
    If Not wksS.AutoFilterMode Then _
    wksS.Range("A1").AutoFilter

    'Filter the "S.DO CONT." for 0 to -100
    With wksS
    If .FilterMode Then .ShowAllData
    .Range("A1").AutoFilter Field:=5, _
    Criteria1:=">=-100", _
    Operator:=xlAnd, _
    Criteria2:="<0"

    'copy it to a new (temporary) sheet
    With .AutoFilter.Range
    On Error Resume Next
    Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
    .SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    End With
    If rng Is Nothing Then
    MsgBox "No data"
    Exit Sub
    Else
    Set rng = .AutoFilter.Range
    rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 1).Copy _
    Destination:=wksTemp.Range("a1")
    End If

    'Get the unique values
    With wksTemp
    .Range(.Range("A1"), Range("a65536").End(xlUp)).AdvancedFilter _
    Action:=xlFilterCopy, CopyToRange:=Range("B1"), Unique:=True
    .Columns(1).Delete
    Set rTemp = .Range(.Range("A1"), Range("a65536").End(xlUp))
    End With
    'extract each sheet of "unique values"
    For Each rCell In rTemp
    .Range("A1").AutoFilter Field:=1, _
    Criteria1:=rCell.Value
    Set rng = .AutoFilter.Range
    rng.SpecialCells(xlCellTypeVisible).Copy

    'create a new word doc and paste the filtered data
    With wdApp
    .Documents.Add
    .Selection.Paste
    End With
    Next
    End With

    'delete the temp sheet
    Application.DisplayAlerts = False
    wksTemp.Delete
    Application.DisplayAlerts = True

    Set rTemp = Nothing
    Set rCell = Nothing
    Set rng = Nothing
    Set wksTemp = Nothing
    Set wksS = Nothing
    Set wdApp = Nothing
    Application.ScreenUpdating = True
    End Sub</pre>


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

    Re: FILTRE AND PAST IN WORD (2000 sr 1)

    OK! ... AND TKS, YOU ARE VERY KINDLE.....
    The code work well but it create many doc on my up bar of windows(?)
    Not is possible to save after the pasting in a dir for example and close the active doc:
    d:mydoc
    with the name of the record extracted for example:

    4500_NAPOLI.DOC
    4501_AGENZIA_1.DOC
    ecc...
    tks for the patience...

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: FILTRE AND PAST IN WORD (2000 sr 1)

    Add these lines:

    <pre> With wdApp
    .Documents.Add
    .Selection.Paste
    'New lines to add
    .ActiveDocument.SaveAs "D:mydoc" & Trim(rCell) & ".doc"
    .ActiveDocument.Close
    'end of new lines
    End With</pre>


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

    Re: FILTRE AND PAST IN WORD (2000 sr 1)

    Sure you are much kind...
    little add in... before the copy in the Word
    Is possible to insert in the last active cell filtred of the column A, the count of value present ...
    for example

    in the last cell of column "C" POSITION NR.
    and in the last cell of column "D" result of count value in A

    and in the last Active cell of the column S.DO CONT the summ of amount for example

    TOTAL Euro -458,25



    Hum.... i have see that.
    the header of the sheet with the name of field is copied only in the first page of doc and if the cell copied is out of the first page the header not is copied...

    Tks for the patience

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

    Re: FILTRE AND PAST IN WORD (2000 sr 1)

    tKS, MANY TKS....
    Difficult to insert this last line in the code...
    .... Do you like to modify for me
    I have already insert the line to save as. ecc...

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: FILTRE AND PAST IN WORD (2000 sr 1)

    <P ID="edit" class=small>(Edited by sdckapr on 05-Nov-04 07:30. Changed new lines to RED for easier indication)</P>How about these modifications?:

    <pre>Sub CodeForSAL()
    <font color=red>Dim lrow As Long</font color=red> 'new line1
    Dim rCell As Range

    'other code

    With wksS
    If .FilterMode Then .ShowAllData
    'New lines1-4
    <font color=red> lrow = .Range("a65536").End(xlUp).Row
    .Cells(lrow + 1, 3).FormulaR1C1 = "POSITION NR."
    .Cells(lrow + 1, 4).FormulaR1C1 = "=SUBTOTAL(3,R[-" & _
    lrow - 1 & "]C1:R[-1]C1)"
    .Cells(lrow + 1, 5).FormulaR1C1 = "=""TOTAL Euro """ & _
    "& SUBTOTAL(9,R[-" & lrow - 1 & "]C:R[-1]C)"</font color=red>
    'end new lines 1-4

    'Other code
    '
    End With
    <font color=red> Application.CutCopyMode = False</font color=red> 'new line5
    Next
    <font color=red> .Rows(lrow).EntireRow.Delete</font color=red> 'new line6
    End With</pre>


    If you want to start changing the word file, you will need a word macro expert.
    You could possibly get the some of the code using words macro recorder and adding with within the
    with wdapp -- end with part
    You could CUT the first row from the sheet to the clipboard and then past it into the header
    Then save the file

    But I don't do much with word so I can't give any more details.

    Steve

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: FILTRE AND PAST IN WORD (2000 sr 1)

    I don't understand why it is "Difficult to insert this last line in the code..."

    The Modifications note which lines are new and which exist. Add the new lines where they are indicated.

    Steve
    PS I changed the new lines to RED in the post to give a better indication of which ones to add.

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

    Re: FILTRE AND PAST IN WORD (2000 sr 1)

    ..:-( Steve,
    Nothing....
    Many error...
    Please re attache my file with your adding line...
    Tks.

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: FILTRE AND PAST IN WORD (2000 sr 1)

    You will have to "translate the message", I do not speak italian. What line causes the error?

    I ran it on the test file and it did not take 10 mins to do the entire thing. Are you using the "sample file" you provided or a different file? How did the "sample file" do?

    If you comment out the line:
    Application.screenupdating= false

    and step thru the code, what lines take a long time to process?

    I recommend stepping thru the code so you can see what the program is doing and you can learn the coding. Knowing what the code does will help you when you get requests for modifications.

    Steve

Page 1 of 2 12 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
  •