1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

macro that can print certain rows

Discussion in 'Software Development' started by AJExcel, Oct 13, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. AJExcel

    AJExcel Thread Starter

    Joined:
    Oct 13, 2008
    Messages:
    12
    Hello Gurus,

    I need a macro that can print selected rows based on a certain cell value for reporting purpose. I have two columns in my work sheet "Task" and "Status". I manually update the status column with cell values as 'open' or 'closed'. My boss wants to check all the tasks that are open.

    How do I write a macro that will pick up all the rows which has cell value for status column as 'open' and and also the corresponding Task

    Finally print it to either a network printer or print it to a word document.
     
  2. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    Why not just use a filter on the status column, i.e. open - for open tasks, and print that?
     
  3. AJExcel

    AJExcel Thread Starter

    Joined:
    Oct 13, 2008
    Messages:
    12
    Yes, right now I have the same setup, but somehow it's not satisfactory. The main sheet has drill down for every task and I update tasks everyday as the resources are allocated to complete the task for a particular app. I have 10 such apps and a worksheet corresponding to each.

    Thanks,

    AJ
     
  4. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    OK - fair enough. In what way is it not satisfactory? What's it missing? Do you want the open tasks for all 10 apps, in one Excel report?

    It's possible, to do what you're asking in Excel, but it sounds as though there is more too it.
    You can loop through your records like this:

    Code:
    Sub OpenJobs()
        Dim rOrigin, rDestination As Range
        Dim Cell As Variant
        Dim i As Integer
            i = 0
        Set rOrigin = Range(Range("Sheet1!A2"), Range("Sheet1!A2").End(xlDown))
        Set rDestination = Range("Sheet2!A2")
        
        Range("Sheet2!A1:B1").Value = Range("Sheet1!A1:B1").Value
        
        For Each Cell In rOrigin
            If Cell.Value = "Open" Then
                rDestination.Offset(i, 0).Value = Cell.Value
                rDestination.Offset(i, 1).Value = Cell.Offset(0, 1).Value
                i = i + 1
            End If
        Next Cell
        
        Worksheets("Sheet2").PrintOut
    
    End Sub
    
    
    If you have 10 workbooks, then this can be integrated to incorporate all the open jobs, into 1 report. Let me know if this is enough, or what else you would like added.

    If it's more complex, it might be better to upload your Excel sheet (with any sensitive data removed), this will give me layout etc.

    How many records are you dealing with in total?
    Thought about an Access DB?
     
  5. AJExcel

    AJExcel Thread Starter

    Joined:
    Oct 13, 2008
    Messages:
    12
    Thanks a lot....Its amazing to receive a reply from you so soon. I sincerely appreciate this. I also appreciate your efforts in helping me out. May be I would upload the Excel sheet and yes I thought about Access and to import the the excel data into it. But I want to keep the efforts I put into this work book. I am not savvy about Excel, so I don't even know how to use the code below, I know to create a button and assign this macro to it...infact I am a DBA working on a migration project....so thought may a sheet like this will help me keep a track of my activities.

    At first I just thought to have a Button in each sheet with a name as "Status Report" and then assign a macro to it which will look for the status "open" and it will print the task which is open along with its status to a word document or to a network printer .

    But now after I came to know that we concatenate I am really interested on how we do it. I have 6 such workbooks each containing two sheets.
     
  6. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    Hey AJExcel,

    First off, forgot to say, Welcome to the forums.

    Secondly, no worries, good to exercise the brain, and helps me hone my skills.
    Do you always use the same workbooks? Or do the names ever change? Either is possible, just requires a little more programming for the second option.
    If you want to distinguish between the various workbooks/sheets then let me know what you want added, suggest an additional column, but a header is also possible, with each section separated by a blank line.

    Would be good if you could upload something with dummy data (sensitive data removed). The data type and workbooks and sheets named the same is necessary - unless you want this to be customisable.

    To add my procedure:
    Go to your Excel sheet, and press ALT+F11 - it'll open up the Visual Basic editor, Right-click where it says "Microsoft Excel Object", select Insert > Module.

    Then paste the code into the module. The Macro OpenJobs now be in the Macros list, and you can assign it to your button, in the main Excel view (you can close the Visual Basic editor, once you've added the code).
     
  7. AJExcel

    AJExcel Thread Starter

    Joined:
    Oct 13, 2008
    Messages:
    12
    Thanks a lot for the welcome...

    I have uploaded a file, this would give you a clear understanding of what I am doing. Sorry I should have done this before. Yes, I use the same workbook always and these files are on our shared drive and I don't move it.

    Once again, I appreciate your efforts.

    AJ
     

    Attached Files:

  8. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    The Excel file in the zipped file is the same as the Excel file.
    Is it possible to post the other Excel files?
    Alternatively, are all the Excel files the same layout? If so just post the name of the files, I may just create the Macro, and let you edit the file names in the VBA code.
     
  9. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    If you look at the picture I've added you'll see some notes, follow these to:
    a. make sure it works past the test sheets in the zip file ( you will need to change the file location for the second workbook, for it to work at all.
    b. To add all 6 of your spreadsheets.

    A button has been added to your first sheet, on the Check List_Test.xls workbook, at the top, press this to run the report.

    Also note the comments in the VBA code, for example, I have commented out the print line so that you can see that it works, without printing it lots of times, and wasting paper.

    Let me know, if anything doesn't work, any error messages you get, and anything you want changed.

    Important Note: A value must be placed in Column A of the last row of your worksheet data (and no other rows), for each sheet, as the macro checks this to determine the total number of records on each sheet, without this it'll still work, but it'll take a lot longer as it'll check every row on the sheet without it.

    Anyway, let me know, how you get on. :)
     

    Attached Files:

  10. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    Oh my! Replace the following line:
    Code:
    Set rOrigin = Sheets(ws).Range(Range("A4"), Range("A4").End(xlDown))
    
    With:
    Code:
    Set rOrigin = Sheets(ws).Range(Range("A4"), Range("B65535").Offset(0, -1).End(xlUp))
    
    You will no longer need to have a value in Column A in any of your sheets.
    I can't believe I didn't do this originally.
     
  11. AJExcel

    AJExcel Thread Starter

    Joined:
    Oct 13, 2008
    Messages:
    12
    MRdnk,

    Excellent work. I would be glad if I could somehow in someway contribute to the forum.

    I tried to understand the code and edited it a little bit. Please excuse me for that. Somethings worked out, like when I hit the button it navigates to the report sheet and I have modified it to pick data from just one work book Infact just one sheet. But I did not succeed, it still picks the data from both the sheets. You are the Guru, so I count on you for the fix.

    Also Is it possible for you to modify the code such that

    1. Each sheet is queried at one time and the results go to the report sheet.
    (OR)
    2. In order to have more clarity, I wish to have 4 separate buttons (Prod Status, UAT status, Cont Status, DEV status) placed on the top or somewhere for each of the servers and once the report is run through one of these 4 buttons, the output would print to the Report sheet. If my boss wants to print the report sheet, he will do it manually.
    For example the range for production server is Row 5 through Row 204, so if the Prod Status button is clicked the code would query these rows and print the out put in the report sheet, and when the UAT status button is clicked the code would query rows 205 to 406 and so on and so forth print the out put in the same report sheet. The reason for this is that the status on production servers is more important and my boss will be interested more in it rather the output from the whole sheet.

    The sheet is not yet standardized, so If I insert rows, I will take care to change the range in the code.

    I apologise to put you through trouble by asking for too much. But if this modification in code is possible and not time consuming for you....Can you please write a code for one button "ProdStatus" on the "Target Server" sheet and I will Take care of the rest.

    Note: It returned me some blank values on the top when I changed the line to

    Set rOrigin = Sheets(1).Range(Range("A4"), Range("B65535").Offset(0, -1).End(xlUp))

    instead of this

    Set rOrigin = Sheets(ws).Range(Range("A4"), Range("A4").End(xlDown))


    Modified Code:

    Sub OpenJobs()
    ' Written by Duncan Wilkie aka MRdnk - October 2008

    Application.ScreenUpdating = False

    Dim rOrigin, rDestination As Range
    Dim sWorkbooks(1) As String
    Dim vBook As Variant
    Dim sSheets(1) As String
    Dim wb As Workbook
    Dim ws As Variant
    Dim Cell As Variant
    Dim i As Integer

    i = 0

    Set wb = Workbooks("Check List_Test.xls")
    Set rDestination = wb.Sheets("Report").Range("C3")
    Range(rDestination, rDestination.End(xlDown).Offset(0, 3)).ClearContents


    sWorkbooks(0) = "Check List_Test.xls"
    sSheets(1) = "Target Server"

    Sheets(1).Select
    Set rOrigin = Sheets(1).Range(Range("A4"), Range("B65535").Offset(0, -1).End(xlUp))

    For Each Cell In rOrigin
    If Cell.Offset(0, 3).Value = "open" Then
    rDestination.Offset(i, 0).Value = Cell.Offset(0, 1).Value
    rDestination.Offset(i, 1).Value = Cell.Offset(0, 3).Value
    i = i + 1
    'rDestination.Offset(i, -1).Value = vBook & " " & ws This line adds a column to the report that informs the user of the
    ' workbook and worksheet, that the data originated from - feel free to remove
    ' Increment the output / report by one for each of the open jobs.
    End If
    Next Cell

    ' Worksheets("Sheet2").PrintOut
    ' The line above is commented out, so that you can see that it works, without printing,
    ' take away the ' at the start of the line to add the print function.

    wb.Sheets("Report").Select ' Back to the original sheet
    Application.ScreenUpdating = True

    End Sub

    Thanks a bunch

    AJ

     
  12. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    I'll take a look this evening if I get a chance, shouldn't take too long.

    You should put any VBA code in the [ CODE ] blocks (without spaces), there is a button "#" on the "Go Advance" option, this'll indent the code and make it easier to read.

    As for your note, the code should be:

    Code:
    Sheets(ws).Range(Range("A4"), Range("B65535").End(xlUp).Offset(0, -1)).Select
    - As it needs to go up before it Offsets.


    My quick thoughts on the various sheets, and reports, is that we should add a pull-down option with a button, to allow you to select the appropriate option - this could then either be on your main workbook (perhaps even a front page with the various options), or a separate report workbook, with the various report options.

    As for the various parts of your sheets, this might be where we use Column A.
     
  13. AJExcel

    AJExcel Thread Starter

    Joined:
    Oct 13, 2008
    Messages:
    12
    Excellent Thought!!.....This way I would have just one button and they would have access to the report in the report workbook without actually accessing the Actual work book. So it means it is not necessary to have all the work book open to actually pull the data?

    Also, If we have a Report work book with a Front sheet which has
    a drop down for the Environment (Prod, UAT..)
    a drop down for the two work sheets (Source server, Target Server)
    a drop down for the 6 work books (alpha, beta, gamma....)

    and then is it possible to concatenate them and get the result out of it...
    like, Beta-->Target Server-->Prod Server or
    Alpha-->Source Server-->Prod Server

    Wow! it would be mind blowing for me and my boss who are not so savvy about Excel to have something like this. And may be I can add some fancy to it when no row is returned, saying "Congratulations Project Completed":)

    THANKS A LOT ONCE AGAIN.....for those amazing thoughts(y)
     
  14. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    Hi there,

    Unfortunately, I didn't get a lot of time to work on this today, however I've started working on the main reports page, and have put a few options in. The attached file, is to wet your appetite. Potentially looking to add options for each of the books and then sheets. Let me know if this is something you may use. I really like the ideas though.

    Have a play with it, and let me know what you think so far. And any changes you would like to see.

    1. In the options section click on the boxes, and change the selection, the custom displays your custom options at the bottom.
    2. Double click on the boxes in the bottom half, to change from ticked to unticked.
     

    Attached Files:

  15. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    Hello Again,

    Try this out for size. Have a play and let me know how you get on, any requests for changes welcome.

    Currently the report workbook, must be in the same directory as the workbooks that you're extracting the data from, and closed. I'm looking to improve on this, and will add this later. However, try this one out for size, hopefully it'll suite your needs.

    Image added to note main page features.
     

    Attached Files:

  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/758866

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice