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

    transfering data value from 2 Wbook... (2000 r s1)

    I have 2 Wbook LISTA_2005 and LISTA_2004.
    Into all 2 Wbook are present many sheets named with the alphabetical
    letter A, B, C.... ecc.

    All sheets named A, B, C ... ecc, contain the first 2 line with header (range
    A to V).
    From the 3th line are presents many value from range A to V.

    I would want to transfer the data value from every letter sheets from LISTA_2004 to every sheets letter into wbook LISTA_2005 in this mode:

    All line (from A3 to V.....) from the sheet named A into LISTA_2004 into
    LISTA_2005 into shest A (add up to existing value)
    All line (A3 to V.....) from the sheet named B into LISTA_2004 into
    LISTA_2005 into shest B (add up to existing value)

    ecc...

    Naturally ad into bottom of every destination sheet the record from source....

    For example if existis 200 record into sheet A copy after line 200 the source value...

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

    Re: transfering data value from 2 Wbook... (2000 r s1)

    You have received many examples that do similar things from the Lounge and other forums. You should be able to write the necessary code yourself.

  3. #3
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: transfering data value from 2 Wbook... (2000 r s1)

    Hans,

    Does the lounge set a limit as to how many questions a member can ask? I am puzzle as on one hand, I read somewhere in the lounge that you don't count how much assistance have been given, but on the other hand, I saw this reply ( maybe 2-3 times in the posts where sal have asked).

    How do you decide when you want to assist and when you don't want to?

    cheers, francis

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

    Re: transfering data value from 2 Wbook... (2000 r s1)

    There is no limit to the number of questions anyone may ask, and all questions are welcome, from beginners and from experienced users.

    But the purpose of the Lounge is to help people with things they don't know yet, in the hope and expectation that they will learn from it, and eventually be able to do it themselves. It is not our goal to write software for free, or to do other people's work.

    Sometimes Loungers forget this, and become a bit too dependent on the Lounge, expecting us to create a small variation on something they have already received help with. If that happens, I may point out that they should be able to work it out themselves using the information they have already been given.

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> Of course, the decision to help somebody or not also depends on my mood, and on whether I have time, and on the feedback I get.

  5. #5
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: transfering data value from 2 Wbook... (2000 r s1)

    Hans,

    Thank for the clarification, <img src=/S/wink.gif border=0 alt=wink width=15 height=15> let me know when you have good mood.

    Quote :
    But the purpose of the Lounge is to help people with things they don't know yet, in the hope and expectation that they will learn from it, and eventually be able to do it themselves. It is not our goal to write software for free, or to do other people's work

    Of course, it would be great to learn and not rely on the Lounge to do the work for us, and hopefully eventually, help out in the Lounge ----- that my aim..

    cheers, francis

  6. #6
    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: transfering data value from 2 Wbook... (2000 r s1)

    I concur with Hans.

    I believe in the adage (and I think it is followed by most of the people responding to questions): "Give a man a fish; you have fed him for today. Teach a man
    to fish; and you have fed him for a lifetime.

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

    Re: transfering data value from 2 Wbook... (2000 r s1)

    My dear teacher Hans,
    after 4 hour of work (now 25/08/2005 3,25 AM!!!!!!!!!) and 2.000 cut and past line i have thsi result, but WHY the macro cannot write the line from source sheet into destination sheet??????????????????????????:
    <img src=/S/confused.gif border=0 alt=confused width=15 height=20> <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45> <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>
    Dim SourceSheet As Worksheet
    Dim DestSheet As Worksheet
    Sub TheMainMan()
    For Each SourceSheet In Workbooks("IPSEMA_2004.xls").Sheets
    If Len(SourceSheet.Name) = 1 And SheetExists(SourceSheet.Name) Then
    Select Case SourceSheet.Name
    Case "A" To "Z", "a" To "z"
    Set DestSheet = Workbooks("IPSEMA_2005.xls").Sheets(SourceSheet.Na me)
    macro3
    End Select
    End If
    Next SourceSheet
    End Sub
    Sub macro3()
    Set SourceRange = SourceSheet.UsedRange
    lastUsedrow = SourceRange.Row + SourceRange.Rows.Count - 1
    If lastUsedrow < 3 Then Exit Sub
    SourceRangeString = "A3:U" & lastUsedrow

    Set DestRange = DestSheet.UsedRange
    DestRow = DestRange.Row + DestRange.Rows.Count '+ 1

    i = 0
    For Each rw In SourceSheet.Range(SourceRangeString).Rows
    If Application.WorksheetFunction.CountIf(DestSheet.Co lumns("B"), rw.Range("B1")) = 0 Then
    DestRangeString = "A" & DestRow + i
    rw.Copy DestSheet.Range(DestRangeString)
    i = i + 1
    End If
    Next rw
    End Sub
    Function SheetExists(SName) As Boolean
    Dim x As Object
    On Error Resume Next
    Set x = Workbooks("IPSEMA_2005.xls").Sheets(SName)
    If Err = 0 Then SheetExists = True Else SheetExists = False
    End Function

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

    Re: transfering data value from 2 Wbook... (2000 r s1)

    Do you get an error message, or does the code run without copying data?

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

    Re: transfering data value from 2 Wbook... (2000 r s1)

    Hans, PERAPHS HAVE SOLVED!

    I have commneted this line in and the trasfering value work fine. In effect i would want to make a filtre on existis record into destination sheet based on a column where is present a ID value for each record. hope you have undesrtand me:

    Sub macro3()
    Set SourceRange = SourceSheet.UsedRange
    lastUsedrow = SourceRange.Row + SourceRange.Rows.Count - 1
    If lastUsedrow < 3 Then Exit Sub
    SourceRangeString = "A3:U" & lastUsedrow

    Set DestRange = DestSheet.UsedRange
    DestRow = DestRange.Row + DestRange.Rows.Count '+ 1

    i = 0
    For Each rw In SourceSheet.Range(SourceRangeString).Rows
    'If Application.WorksheetFunction.CountIf(DestSheet.Co lumns("L"), rw.Range("L1")) = 0 Then
    DestRangeString = "A" & DestRow + i
    rw.Copy DestSheet.Range(DestRangeString)
    i = i + 1
    'End If
    Next rw
    End Sub

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

    Re: transfering data value from 2 Wbook... (2000 r s1)

    Shouldn't

    SourceRangeString = "A3:U" & lastUsedrow

    be

    SourceRangeString = "A3:V" & lastUsedrow

    You wrote that you wanted to copy columns A through V.

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

    Re: transfering data value from 2 Wbook... (2000 r s1)

    .... sorry but the correct range is A to U

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

    Re: transfering data value from 2 Wbook... (2000 r s1)

    Peraphs i have solved to test the dupes...
    But 2 little modify:
    1)
    Assuming into all 2 wbook (LISTA_2004 and LISTA_2005) i have many sheets named A,B,C,D,E... ecc how to sort in "one shot" the range A:U indexed from column G...

    2) Assuming in each sheest A, B, C, D..ecc in D1 for all i have a number (result of =CONTA.VALORI(A3:A65536)) how to summ for each D1 cells and copy the total into sheet CONTROLLO always in D1?

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

    Re: transfering data value from 2 Wbook... (2000 r s1)

    1) You'll have to loop through the worksheets and if the name is in the range "A" to "Z", sort it on column G.

    2) Try this formula in cell D1 on the CONTROLLO sheet:

    =SUM(A:Z!D1)

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

    Re: transfering data value from 2 Wbook... (2000 r s1)

    Hans, if i copy this formula into CONTROLLO sheet before all sheets are created i have:

    #NOME?

    ut if i copy the formula after all sheets are created all work fine?!

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

    Re: transfering data value from 2 Wbook... (2000 r s1)

    Before creating the sheets, there are no sheets named A and Z in the 2005 workbook, so the formula is not valid yet. You can create or copy the formula from the 2004 workbook into the 2005 workbook after creating the sheets.

Posting Permissions

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