Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Writing visible records (2002/SP-2)

    User interacts with a "Master" sheet (database type layout) using auto filters to limit number of records. User then wants to create "Assignment" workbooks with no more than 40 records each based only on the filtered records from the "Master"; a filtered list of 160 records would create 4 separate "Assignment XXX" workbooks, for example.

    Problem: How to loop through only visible cells. My current approach is very ham-handed - copying only visible cells to another workbook and then looping through that new workbook to create each of the Assignment sheets. I would think there must be a way to avoid that intermediate step.

    BTW, I am couple of years removed from what limited VBA skills I once had, and I must have searched this site 10 times over the last several days to get answers to other questions on this project. I really appreciate what a resource this Lounge is.

  2. #2
    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: Writing visible records (2002/SP-2)

    You can set a range to get only the visible cells:

    Set rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisib le)

    Then you can loop thru each area of the range and each row of the area to do what you want

    For Each rArea In rng.Areas
    For x = 1 To rArea.Rows.Count
    'your code here
    Next
    Next


    This is general, Let me know if you need additional details.

    Steve

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Writing visible records (2002/SP-2)

    This comes up a lot, but Searching for it isn't easy because the Post wording is not necessarily close to the topic. Have a look at <!post=this thread,134802>this thread<!/post>, where <!profile=SammyB>SammyB<!/profile> shows how to use AutoFilter in VBA and <!profile=LegareColeman>LegareColeman<!/profile> shows how to code the same outcome without using AutoFilter. If you need more specific assistance, post a censored workbook example showing what your data and filter criteria look like.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing visible records (2002/SP-2)

    Steve and John -

    I think this is exactly what I needed. Thanks!

    Bill

  5. #5
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing visible records (2002/SP-2)

    Steve -

    Was not able to figure out how to use your suggestion when the visible area covered hidden columns as well as rows. I could never tell for sure where I was as the loops were executing since one area might be to the right of the one previous; then the next one would be below and to the left. Doing something wrong probably.

    Ended up refining my "ham-handed" approach by adding some error checking and replacing many of my select statements with activate. A little awkward - and I'm sure if I knew what I was doing I could even clean that up more - but it is working.

    Thanks for the help. I'm sure I'll be able to use those concepts in another setting.

  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: Writing visible records (2002/SP-2)

    If you only want to test items in a particular column, you can intersect the "set rng" with the column, then when you go thru the list, you only have go thru the rows (for example)

    Set rng = intersect(ActiveSheet.UsedRange.SpecialCells(xlCel lTypeVisible),Range("A:A"))

    to get all the visible cells in the used range in col A.

    Steve

  7. #7
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing visible records (2002/SP-2)

    I think I understand. The problem I would still have is writing a complete (visible) record to another workbook when several nonadjacent columns are hidden, and the macro would not know prior to runtime which columns are hidden.

    But again - you've introduced me to "intersect" - another concept that I know will come in handy - THANKS.

  8. #8
    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: Writing visible records (2002/SP-2)

    It should not be an issue with hidden columns. As you loop thru the rows you can loop thru each cell of the row and "transfer" any cell whose column is visible

    Could you provide a sample sheet with some hidden columns and a filter and explain what you want the macro to do (show the final output)?

    Steve

  9. #9
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing visible records (2002/SP-2)

    Here is the sample. Prior to macro execution user filters records down to a subset that s/he then wants to assign to others to work.

    Macro would need to...
    > Prompt user for an "Assignment Name"
    > Colapse MasterFile column outline levels (if user has not already done so) to make sure only assignment-related columns are visible
    > Loop to...
    >>> Open "Assignment_Blank.xls" workbook
    >>> Write 40 visible records to Assignment_Blank.xls
    >>>>>> Assign each visible record an Assign_Date (= AssignmentName_CurrentDate_AssignmentRecord#; where Assignment Record# = 1 thru X number of visible records)
    >>>>>> Assign_Date will also be written to the Assignment_Blank.xls
    >>> SaveAs (Assignment_Blank.xls) as AssignmentName_N.xls (where N is a sequence incremented with each new file)
    >>> Close AssignmentName_N.xls
    >>> If more visible records on MasterFile then loop

    I currently have all the above working. BUT my version uses a "Selection.SpecialCells(xlCellTypeVisible).Sel ect" command on the MasterFile and then copies and pastes that into a new "temp" workbook. I then process the "temp" workbook: sending the calculated Assign_Date back to the MasterFile; sending 40 records at a time to my AssignmentBlank.xls; using "Application.GetSaveAsFilename" (including a calculated InitialFilename) to save (and then close) each 40-record assignment file; until I run out of records. I then close the "temp" file. My macro is in it's own workbook - with a lot of other project-related file manipulation procedures - so is not included with this MasterFile.

    The sample MasteFile I attached has 45 records - which would mean one Assignment file with 40 records and a second one with only 5.

    Steve, I am very interested in finding out the right way to do this (or a better one), but the version I created is working - so I'd hate for you to spend a lot of time picking through this mess if you have better things to do <img src=/S/doh.gif border=0 alt=doh width=15 height=15> .

  10. #10
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing visible records (2002/SP-2)

    Here is the first Assignment file my macro created also

  11. #11
    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: Writing visible records (2002/SP-2)

    I don't have time to do too much it now, but to answer your original question try some code like this:

    <pre> Dim wkb As Workbook
    Dim wksMFT As Worksheet
    Dim wksAT As Worksheet
    'other DIMs

    Set wkb = Workbooks("Woodys1_001-040.xls")
    Set wksMFT = Worksheets("MasterFileTable")
    Set wksAT = wkb.Worksheets("AssignmentTable")
    'other code

    wksMFT.UsedRange.SpecialCells(xlCellTypeVisible).C opy _
    wksAT.Range("a1")

    'other code
    Set wksMFT = Nothing
    Set wksAT =Nothing
    Set wkb = nothing
    </pre>


    You don't have to worry about areas or looping if you are just looking at copying the entire filtered range.

    Steve

Posting Permissions

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