Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    New Lounger
    Join Date
    Sep 2013
    Posts
    14
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Extracting data in Excel

    i have an excel file with 5 tabs

    the first 4 tabs contain products: the tab content is as follow

    artikel number/Information/Price/Quantity/ Total/Confirm


    now what i want to do is: on tab number 5 is the same content as the other 4 tabs,
    but on this tab, al the artikels (rows) that are confirmed (by entering '1') on the other 4 tabs,
    have to be displayed together on the 5'th tab

    so lets say i have

    artikel number/Information/Price/Quantity/ Total/Confirm

    2/Ball/0.10$/2pc/0.20$/0
    3/pencil/0.50$/2pc/1$/1
    4/book/1$/4pc/4*/0
    5/spoon/0.10/5pc/0.50$/1

    the 5 tab has to display

    3/pencil/0.50$/2pc/1$/1
    5/spoon/0.10/5pc/0.50$/1


    can any of you help me with this?

    i would greatly appriciate it.


    sorry for the english i'm a belgian guy

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Could you attach an example file with 4 examples sheets and 5th sheet that shows what you want to be extracted...

    Steve

  4. The Following User Says Thank You to sdckapr For This Useful Post:

    privatejd (2013-09-27)

  5. #3
    New Lounger
    Join Date
    Sep 2013
    Posts
    14
    Thanks
    9
    Thanked 0 Times in 0 Posts
    hi guyst

    So my custumor needs:

    Artikel nr with anmount and tax%:
    6
    17
    48
    49
    67
    75
    120

    So what i want:

    - If artikel is ordered (amount and tax% are filled in) it has to be GREEN
    - Al the artikel lines that are ordered must be on page 5 (example see Example tab)


    al of the above must be completed automaticly so custumors can use it

    Thnx guysDeclercq Joren.xlsx

  6. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    If you add this code to the "Example" worksheet in VBA

    Code:
    Option Explicit
    Private Sub Worksheet_Activate()
      Dim i As Integer
      Dim sRange As String
      Dim lRows As Long
      
      lRows = 31
      sRange = "A2:H" & lRows
      Range(sRange).ClearContents
      For i = 1 To 5
        With Worksheets("Tab" & i)
          .Range("A1").AutoFilter
          .Range("A1").AutoFilter Field:=3, Criteria1:="<>"
          If Evaluate("Subtotal(2, 'Tab" & i & "'!A2:A" & lRows & ")") <> 0 Then
            .Range(sRange).Copy Range("A" & lRows + 1).End(xlUp).Offset(1, 0)
          End If
          .Range("A1").AutoFilter
        End With
      Next
    End Sub
    Whenever that sheet is activated, it will clear the region, then loop through the 5 "Tab" sheets, filter on Column C, copy the items to the example sheet.

    Steve

  7. #5
    New Lounger
    Join Date
    Sep 2013
    Posts
    14
    Thanks
    9
    Thanked 0 Times in 0 Posts
    what would i have to ajust if i would like to start displaying for lets say A6 on the example page

    i will explain.

    on the exaple page there wil be text above the formula like:

    hello

    thank you for chosing.....

    and then

    the table would have to appear

    so infact i want the table to start at a10 instead of a1 on the example page

  8. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    If you wanted the table to start at row 10 why did you put it in row1 in the example? It is important that the example represents the problem so the solution meets the requirements. I have added a 2nd "range" for the output, since the range in the 5 tabs is no longer the same in the output. Change these as desired.
    Code:
    Option Explicit
    Private Sub Worksheet_Activate()
      Dim i As Integer
      Dim sRange As String
      Dim sRngTable As String
      Dim lRows As Long
    
      sRngTable = "A11:H40" 'Adjust as needed
      lRows = 31 'Adjust as needed
      sRange = "A2:H" & lRows 'Adjust as needed
      Range(sRngTable).ClearContents
      For i = 1 To 5
        With Worksheets("Tab" & i)
          .Range("A1").AutoFilter
          .Range("A1").AutoFilter Field:=3, Criteria1:="<>"
          If Evaluate("Subtotal(2, 'Tab" & i & "'!A2:A" & lRows & ")") <> 0 Then
            .Range(sRange).Copy Range("A" & lRows + 1).End(xlUp).Offset(1, 0)
          End If
          .Range("A1").AutoFilter
        End With
      Next
    End Sub
    Steve

  9. #7
    New Lounger
    Join Date
    Sep 2013
    Posts
    14
    Thanks
    9
    Thanked 0 Times in 0 Posts

    excuse

    it was originally ment to get data out of this,

    but now boss wants to ad head logo and all that above the table

  10. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    If the data sets change (the 5 tabs) the code would have to be modified as well. Note that code hard codes and uses the Tab1 - Tab5 nomenclature so changing the tab names will also impact the macro.

    Let us know if you need additional help or want the logic of the code explained.
    Steve

  11. The Following User Says Thank You to sdckapr For This Useful Post:

    privatejd (2013-10-02)

  12. #9
    New Lounger
    Join Date
    Sep 2013
    Posts
    14
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Declercq Joren.xlsm

    What i mean is,

    on page "Totaal" the Table must start at the point i wish


    now it starts at A1

    But i has to start on A10 so i can ad a banner and head of company to it.

    i hope this is clear?

    if not just say so

  13. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    The new code I posted (with the 2 ranges) adapted for that ...

    Steve
    PS you will need the line:
    DrawingObjects.Delete

    After the clearcontents line to delete the objects that you have now included, so the copy can add new ones...
    [Note: If you have any drawingobjects you don't want to delete, you will have to loop through the objects and only delete particular ones and not delete others...]
    Last edited by sdckapr; 2013-10-01 at 11:13. Reason: Added PS about drawing objects

  14. The Following User Says Thank You to sdckapr For This Useful Post:

    privatejd (2013-10-02)

  15. #11
    New Lounger
    Join Date
    Sep 2013
    Posts
    14
    Thanks
    9
    Thanked 0 Times in 0 Posts
    first things first : steve, thank you for all you done so far.

    my sensire gratitude.

    now i have used your code,

    but as you say, the pictures keep appearing on top of each other, so i was wondering where do i have to put the DrawingObjects.Delete command.

    ty for all

  16. #12
    New Lounger
    Join Date
    Sep 2013
    Posts
    14
    Thanks
    9
    Thanked 0 Times in 0 Posts
    ok, the drawing objects is solved

    but the 2range thing doesnt work,

    it still copy's some text in box a2-j10
    an then it starts from a11-j.. again
    Last edited by privatejd; 2013-10-02 at 02:41.

  17. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Perhaps an example which demonstrates the problem. It should not copy above the header row.

    One line that is probably an issue is finding the last row to copy to:

    Change to:
    .Range(sRange).Copy Range("A" & .rows.count).End(xlUp).Offset(1, 0)

    The last row to copy had been extended and that was not fixed.

    Steve

  18. The Following User Says Thank You to sdckapr For This Useful Post:

    privatejd (2013-10-02)

  19. #14
    New Lounger
    Join Date
    Sep 2013
    Posts
    14
    Thanks
    9
    Thanked 0 Times in 0 Posts
    its not working as it should, maybe i am doing somthing wrong.

    i have another question Steve,


    for the imiges in the document i have this code,


    Code:
    Sub FotosToevoegen()
        Dim lRow As Long, lLoop As Long
        Dim sShape As Shape, myarray As Variant
            myarray = Range("J2", Range("J65536").End(xlUp)).Value
            myarray = Application.WorksheetFunction.Transpose(myarray)
            ActiveSheet.Protect False, False, False, False, False
    GetPict:
        If Not IsArray(myarray) Then
            MsgBox "Slechts 1 bestand geselecteerd."
            Exit Sub
        End If
        On Error Resume Next
        lRow = 2
        For lLoop = LBound(myarray) To UBound(myarray)
        Set sShape = ActiveSheet.Shapes.AddPicture(myarray(lLoop), msoFalse, msoCTrue, _
                Cells(lRow, 9).Left, Cells(lRow, 9).Top, Cells(lRow, 9).Width, Cells(lRow, 9).Height)
            With sShape
                .Placement = xlMoveAndSize
            End With
        lRow = lRow + 1
    Next lLoop
    End Sub
    but its page dependend.
    if i make a button on total page,

    it only adds the foto's on the total page, not the other 4 tabs...

  20. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    its not working as it should, maybe i am doing somthing wrong.
    I can't answer this. I don't know what it "should do", nor do I know what it is doing in YOUR workbook. It works as I think you have described that it should in the sample workbooks you have attached (and that I have modified with a different start row). What is it doing that it should not, or what should it be doing that it does not? You need to be specific.

    but its page dependend.
    Your code is dependent on the "ActiveSheet". If you have button to run the code, you must make sure that it makes the appropriate sheet active or you could do away with the active sheet references (the explicit and implicit) and loop through the sheets using a worksheet range object.

    Your example worksheets don't have any filenames and paths in column J so that is one reason why they would not get pictures, the total page is the only one with the information.

    If you want either item looked at, please attach a sample that demonstrates the issues and explain the problems, indicating what the code does or does not do.
    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
  •