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

    compare and copy only (2000 sr 1)

    from this 2 sheet is possible with a macro compare and copy from anagrafe in the sheet sportelli only a data with not duplicate data

    for example if in the sportelli not is present

    175070016 PORTAF.16 CENTRO CORPORATE CAMPANIA NORD

    but is present in anagrafe

    put this in the bottom of sportelli

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

    Re: compare and copy only (2000 sr 1)

    Do you want the macro to compare and copy ALL rows of the Sportelli sheet to Anagrafe, or do you want it to compare and copy only the rows that are currently selected?

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

    Re: compare and copy only (2000 sr 1)

    invers if int sportelli not is present from the olready exixting code copy from anagrafe in to sportelli

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

    Re: compare and copy only (2000 sr 1)

    I'm sorry, I was getting confused because the data in Anagrafe were offscreen, so I thought that it was empty. Is it sufficient to compare the only the code? Or is it necessary to check both code and name?

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

    Re: compare and copy only (2000 sr 1)

    only code, because the code is linked to the description...

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

    Re: compare and copy only (2000 sr 1)

    Here is a macro:
    <pre>Sub Copy2Sportelli()
    ' Declare variables
    Dim lngSourceRow As Long
    Dim lngStartRow As Long
    Dim lngEndRow As Long
    Dim lngTargetRow As Long

    ' Set error handling
    On Error GoTo ErrHandler

    ' Speed up by not displaying changes while macro is running
    Application.ScreenUpdating = False
    Application.Cursor = xlWait

    ' Initialize variables
    lngStartRow = 5
    lngEndRow = Worksheets("Anagrafe").Range("I65536").End(xlUp).R ow
    lngTargetRow = Worksheets("Sportelli").Range("B65536").End(xlUp). Row

    ' Loop through rows
    For lngSourceRow = lngStartRow To lngEndRow
    ' Keep user informed
    Application.StatusBar = "Processing row " & lngSourceRow
    ' Compare codes
    If Worksheets("Sportelli").Range("B1:B" & lngTargetRow).Find _
    (Worksheets("Anagrafe").Range("I" & lngSourceRow)) Is Nothing Then
    ' Next free row
    lngTargetRow = lngTargetRow + 1
    ' Copy cells
    Worksheets("Anagrafe").Range("I" & lngSourceRow & ":J" & lngSourceRow).Copy _
    Worksheets("Sportelli").Range("B" & lngTargetRow)
    End If
    Next lngSourceRow

    ExitHandler:
    ' Clean up
    Application.StatusBar = False
    Application.CutCopyMode = False
    Application.Cursor = xlDefault
    Application.ScreenUpdating = True
    Exit Sub

    ErrHandler:
    ' Inform user, then clean up
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub
    </pre>

    I have attached the (zipped) workbook with the macro.

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

    Re: compare and copy only (2000 sr 1)

    ... CLEAR THE COMMENT IN THE MACRO
    ... CLEAR THE WORK
    ... BEST THE AUTOR

    ****** SIX STAR CODE (STANARD WONDERFULL IS 5 START;-)

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

    Re: compare and copy only (2000 sr 1)

    WIZARD, is possible to count only a a code whitout duplicate, in the column "I" of anagrafe, and insert the result in the cell "H1" of this sheet?
    With a formula or the same with a macro...

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

    Re: compare and copy only (2000 sr 1)

    This is not trivial, it involves advanced methods that would take too much space to explain here.

    In the first place, change the number format for cell H1 to general, for it is formatted as text now.
    Next, we are going to define a dynamic named range. See Named Ranges for more information.
    Select Insert | Name | Define...
    Type UFFICIO in the Names box, and enter this formula in the Refers to box:

    =OFFSET(ANAGRAFE!$I$5,0,0,COUNTA(ANAGRAFE!$I$5:$I$ 65536),1)

    This name will refer to the range from I5 to the last filled cell in column I (there should be no gaps).
    In cell H1, enter this formula:

    =SUM(1/COUNTIF(UFFICIO,UFFICIO))

    It is an array formula, it must be confirmed with Ctrl+Shift+Enter. This will place brackets { } around the formula. You must NOT type those brackets yourself, Excel will not recognize them.

    I have attached the zipped workbook again.

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

    Re: compare and copy only (2000 sr 1)

    Little modify for this code.
    Ok for all code of the macro, but is possible to make this opeartion to alla code present in B that init only for the first 2 character init for 45 or 65 skip another....

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

    Re: compare and copy only (2000 sr 1)

    I'm sorry, I don't understand your question. Originally, you wanted to copy data from column I in Anagrafe to column B in Sportelli.

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

    Re: compare and copy only (2000 sr 1)

    i am sorry , but in this moment my head thinging only a sea, beach and vacation....;-)

    i reattache new file

    The copy and compare is from anagrafe column G to sportelli column A copy without the the record that not init for 45 or 65...

    (Sorry but i have modifyed your original code for my another procedure in the same wbook...)

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

    Re: compare and copy only (2000 sr 1)

    There is no macro in the workbook you attached.

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

    Re: compare and copy only (2000 sr 1)

    (Edited by HansV - Script is NOT a good font for code! Inserted <!t>[pre]<!/t> and <!t>[/pre]<!/t> tags instead.)

    hereis the code to refer column B and I posted from you in nr. Post: 389768 in this same post:
    <pre>Option Explicit

    Sub Copy2Sportelli()
    ' Declare variables
    Dim lngSourceRow As Long
    Dim lngStartRow As Long
    Dim lngEndRow As Long
    Dim lngTargetRow As Long

    ' Set error handling
    On Error GoTo ErrHandler

    ' Speed up by not displaying changes while macro is running
    Application.ScreenUpdating = False
    Application.Cursor = xlWait

    ' Initialize variables
    lngStartRow = 5
    lngEndRow = Worksheets("Anagrafe").Range("I65536").End(xlUp).R ow
    lngTargetRow = Worksheets("Sportelli").Range("B65536").End(xlUp). Row

    ' Loop through rows
    For lngSourceRow = lngStartRow To lngEndRow
    ' Keep user informed
    Application.StatusBar = "Processing row " & lngSourceRow
    ' Compare codes
    If Worksheets("Sportelli").Range("B1:B" & lngTargetRow).Find _
    (Worksheets("Anagrafe").Range("I" & lngSourceRow)) Is Nothing Then
    ' Next free row
    lngTargetRow = lngTargetRow + 1
    ' Copy cells
    Worksheets("Anagrafe").Range("I" & lngSourceRow & ":J" & lngSourceRow).Copy _
    Worksheets("Sportelli").Range("B" & lngTargetRow)
    End If
    Next lngSourceRow

    ExitHandler:
    ' Clean up
    Application.StatusBar = False
    Application.CutCopyMode = False
    Application.Cursor = xlDefault
    Application.ScreenUpdating = True
    Exit Sub

    ErrHandler:
    ' Inform user, then clean up
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub
    </pre>


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

    Re: compare and copy only (2000 sr 1)

    Yes, I know that. There is no point in repeating code that is already available in the same thread. But that code does not apply to the new workbook you posted.

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
  •