Page 1 of 3 123 LastLast
Results 1 to 15 of 45
  1. #1
    Lounger
    Join Date
    Sep 2011
    Posts
    25
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Extract from multiple sheets - revisited

    With reference to the KWEAVER 2015-01-28 10:36 Topic, and Maudibe/Zeddy's response, I have an almost identical requirement that I would appreciate some assistance with.

    The source w/book contains a sheet for each month - starting from April 2014 eg Apr14, May14,..Jan15, plus eventually Feb15 & Mar15, to cover the 12 mth Financial Year in this particular situation). Each sheet contains 20-30 detail lines of supplier invoice data relating to maintenance & other costs for a 30 unit apartment building.

    The process would be the same, ie create a new W/Book with a sheet for each unique Apartment No. & populate this with the relative detail lines for that Apartment extracted from the source w/book.

    Note that I use Excel 2002 SP3 & Win XP/SP2 on a Notebook for this application, and have not have any problems with other VBA solutions - one of which the above-mentioned provided a year or so ago. I was able to convert & run the test file sent to Kweaver & it created the 'Temp' sheet with all the extracted lines, but hung on the 'removeduplicates' command line (Excel 2002 issue?).

    The selection of sheets in the source file for the extract step might need some tweaking, as at present there are other monthly sheets in the same W/book that I don't want included in the extract. The required sheets are all in a contiguous block at the left-hand end of the list, so could the selection be 'Apr14 & all sheets to the left of it', or is there an internal sequence no. that could be used as a range, or perhaps use Ctrl/Click to highlight the required sheets for example. I can always copy the required sheets to a new W/Book before each update if necessary.

    The attachment xls file contains three test source sheets & a 'sample only' output sheet for one of the Apartments, as well as some further Notes regarding various fields etc.

    Let me know if you require any further information. Its not a rush job, if you have other priorities at present.[/SIZE][/SIZE]
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    While not a VBA expert, I'm sure someone will come up with a solution.
    They data representation is not the same as mine was where the VBA solution worked.

    [PS, I've done your situation using an array processing language, but I know that's not what you want]

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    John,

    I have adapted the file by replacing the incompatible RemoveDuplicate code with a method compatible for 2002+. On the created temp sheet it creates a helper column next to the reverse sorted apartment numbers in col A and insets the formula in B1 =COUNTIF($A$1:$A1,A1)>1 copied down. The helper column cells will be FALSE for the first instance of the value and then TRUE for the duplicates. The final lines of the code delete all rows with TRUE values (duplicates) leaving a unique list. This list is used to name the individual sheets in the new workbook.

    RemoveDups2.png

    Code:
    '------------------------------------
    'REMOVE DUPLICATES
        LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        Range("B1").Formula = "=COUNTIF($A$1:$A1,A1)>1"
        Range("B1").Select
        Selection.AutoFill Destination:=Range("B1:B" & LastRow), Type:=xlFillDefault
        For I = LastRow To 1 Step -1
            If Cells(I, 2) = True Or Cells(I, 1) = "" Then
                Cells(I, 2).EntireRow.Delete
            End If
        Next I
    The rest of the code has been modified to match your data and format. I was a bit unclear what extra sheets the source book contains, so since this code depends on counting the sheets, it assumes that the source workbook contains ONLY the monthly sheets that have the desired data.

    HTH,
    Maud
    Last edited by Maudibe; 2015-02-20 at 06:01.

  4. #4
    Lounger
    Join Date
    Sep 2011
    Posts
    25
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Maud,

    Thanks for the prompt response. It was under the radar for the last few days because I didn't receive any notification of it - just one for the first response. I did visit the Forum to check this, as the email stated, but didn't log-in. Maybe that's the reason.
    I've run the code provided, but it stops with a 'Run-time error 438, Object doesn't sort this property or method' on the second line after the 'Sort List heading,
    ie ActiveWorkbook.Worksheets("Temp").Sort.SortFields. Clear
    The Temp folder has been created but only shows the raw data in Col A (as extracted from Col. R in the source folders). Am I missing something here?

    Note that the xlsm file you sent is being 'converted' to xls compatibility before the workbook is displayed. Is this causing the problem? Can you send me an xls source file version to test perhaps?

    Thanks again.
    John

  5. #5
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    As OP said needs to work in lower version. See attached assumes destination sheet is always to the left and montly sheets ONLY are to the right.
    Attached Files Attached Files
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  6. #6
    Lounger
    Join Date
    Sep 2011
    Posts
    25
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Don, Very perceptive of you....That type of lookup facility was precisely what I was originally playing around with (notably unsuccessfully), until I came across Maude's solution that will give me a more comprehensive view, ie the best of both worlds.
    Just a minor tweak required to pick up Rows with a 'blank' Ref. field (Col. D) eg Mar14 R33-35, 39, 41-42.
    Also, any chance of a 'Total' value being added at the bottom of Col F on the GetData worksheet? Didn't want to make it too easy for you!!
    Many thanks for your solution.
    John

  7. #7
    Lounger
    Join Date
    Sep 2011
    Posts
    25
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Maude, Apologies for the extra work required. I don't mind doing some ad-hoc type testing for you if that helps.
    John

  8. #8
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Just a minor tweak required to pick up Rows with a 'blank' Ref. field (Col. D) eg Mar14 R33-35, 39, 41-42.
    Not quite sure what you mean here. Send me sample illustration.

    Total is NOT a problem either at the bottom or BETTER at the top
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  9. #9
    Lounger
    Join Date
    Sep 2011
    Posts
    25
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Supershoe View Post
    Just a minor tweak required to pick up Rows with a 'blank' Ref. field (Col. D) eg Mar14 R33-35, 39, 41-42.
    Not quite sure what you mean here. Send me sample illustration.

    Total is NOT a problem either at the bottom or BETTER at the top
    Thks Don,

    Response as attached.

    John
    Attached Files Attached Files

  10. #10
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Somehow I was under the impression that you did NOT want data where the ref column was blank???
    I commented out the line to clear that row if the ref column is blank.
    Is this what you need.
    See att
    Attached Files Attached Files
    Don Guillett
    Excel Developer
    dguillett @gmail.com

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

    johnrfnz (2015-02-18)

  12. #11
    Lounger
    Join Date
    Sep 2011
    Posts
    25
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Excellent work Don, much appreciated.

    My reference to ignoring 'blank' lines was to Rows with Col. R blank - just a means of by-passing 'non-data' lines in my source W/Sheets. All fixed now.

    If I needed to 'transfer' this function to an existing W/Book, would it just require the GetData sheet to be copied to the left-most position, as well as the VBA code (unchanged?)?
    Also ensure that only valid monthly sheets were to the right of this.
    I don't need to do this yet but thought I'd raise it while we were on the subject.
    Many thanks again.
    John

  13. #12
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Post your final file that works as desired. The macro, as written, assumes only the getdata sheet and the ONLY the monthly sheets to the right. Just use the getdata sheet and the code module which can be "dragged" to your file. OR, use my file>delete the existing test monthly sheets and add your monthly sheets. Post your final.
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  14. #13
    Lounger
    Join Date
    Sep 2011
    Posts
    25
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Maud,

    That's now working fine, except for an issue with the sequence of data rows in the new W/sheets. The Date column is correct, but the other fields are in reverse order. #227 as an example shows dates of 01/31, 02/28, 03/31, & 03/31 in the correct sequence, but the data in Cols B to F is in the reverse order, ie does not match the date column eg Row B5-F5 should be in B2-F2 with an amount of $903.78. Like-wise B4-F4 should be in B3-F3. Hope that makes sense. Let me know if you need further info. on this.

    Some formatting 'preferences':
    - Sequence of W/Sheets to be in ascending seq. left to right
    - Col. A & Col. C to be left-aligned
    - Total to be moved one line down (ie leave a spare line above it)

    Wish-List: (can be deferred)
    - Calculate a 'Grand Total' across all new W/Sheets & display this at the end of the update. No need to store it - just provide an 'OK' response box to remove the display.

    - The Run Code option will not need to be run each time the source W/Book is opened. Can there be a Dialog Box displayed each time it is opened giving a 'Run Code Y/N' option to be selected (with N being the default, or maybe a tick-box for a Y response, or other variation that will provide such a user-option)?

    Thanks,
    John

  15. #14
    Lounger
    Join Date
    Sep 2011
    Posts
    25
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Don,

    'Final' version as attached. Note mods. to Labels in Row 2.

    Thanks again. John
    Attached Files Attached Files

  16. #15
    Lounger
    Join Date
    Sep 2011
    Posts
    25
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Hi Don,

    Can you check the attached details please. Seems to be a natural extension of the present look-up process that would be quite valuable. Please let me know what you think. No rush.
    Thanks,
    John
    Attached Files Attached Files

Page 1 of 3 123 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
  •