# macro that can print certain rows



## AJExcel (Oct 13, 2008)

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.


----------



## MRdNk (Apr 7, 2007)

Why not just use a filter on the status column, i.e. open - for open tasks, and print that?


----------



## AJExcel (Oct 13, 2008)

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


----------



## MRdNk (Apr 7, 2007)

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:


```
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?


----------



## AJExcel (Oct 13, 2008)

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.


----------



## MRdNk (Apr 7, 2007)

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).


----------



## AJExcel (Oct 13, 2008)

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


----------



## MRdNk (Apr 7, 2007)

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.


----------



## MRdNk (Apr 7, 2007)

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.


----------



## MRdNk (Apr 7, 2007)

Oh my! Replace the following line:

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

```
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.


----------



## AJExcel (Oct 13, 2008)

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


----------



## MRdNk (Apr 7, 2007)

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:


```
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.


----------



## AJExcel (Oct 13, 2008)

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:up:


----------



## MRdNk (Apr 7, 2007)

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.


----------



## MRdNk (Apr 7, 2007)

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.


----------



## MRdNk (Apr 7, 2007)

No adverts please, and inappropriate place to put these posts, anyway.

(Edit: Advert now removed)


----------



## AJExcel (Oct 13, 2008)

Hello MRdNk,

This is an excellent effort. Unfortunately some issues have come up. All the combination for Alpha and Gamma work, but they do not populate data in the Report sheet. I have attached for your understanding some screen shots of the errors I am receiving for all different combination of Beta. I tried to play around to fix errors but then some other error poped up, and so I didn't mess around more with it. Thought it might break the code.

How do I add more apps to the list box...I know how to do it with combo box. Is it the same way?

Thanks,

AJ


----------



## MRdNk (Apr 7, 2007)

Are all the workbooks in the same folder? As well as the report workbook?

Edit the following line:
.Open Filename:=sFileName

To:
.Open Filename:=vBook

Let me know if that works.


----------



## MRdNk (Apr 7, 2007)

Apologies for the error messages. 

I've added some additional code, that'll get the workbook from any folder, as long as the correct files and their directories are properly written in the list (column U) - they can now be in any folder.

v1.2 attached.

There are more issues, that I'm working on to smooth out user error, and fix some of the bugs.
I want to work on it a little more first, and then give you an updated version.
Fixes so far:
1. All Workbooks available in pull-down.


----------



## AJExcel (Oct 13, 2008)

Please don't apologize. I am grateful to you, for your efforts. I am not in a rush so you can do it at your leisure. 

Thanks,

AJ


----------



## AJExcel (Oct 13, 2008)

Hello MRdNk,

Hope you had a good weekend so far. I was wondering if you got a chance to look at the code again. Again, I am not in a rush so please take your time. 

The new code does not return errors, but it is not populating data in the report sheet. 

Thanks, I'll appreciate all your efforts.

AJ


----------



## MRdNk (Apr 7, 2007)

Yeah, pretty good weekend, you? 

Try this latest version out for size, I've fixed the issues, you can now use files stored anywhere on your hard-drive, as long as you put the filename and location into the sheet, it'll pick them all up.Try it out and let me know if you get any errors, and what they are. Screen-shots great.


----------



## AJExcel (Oct 13, 2008)

Sorry to put you through this, but there are some errors and I am unable to fix them.....Please find attached the errors


----------



## MRdNk (Apr 7, 2007)

No worries. I should have tested that properly, give me a couple of days, and I'll fix it to work properly, including better testing.


----------



## Zack Barresse (Jul 25, 2004)

@MRdNk: You should think about explicitly qualifying your objects (i.e. linking the sheets the the workbook, and ranges to said sheets). Is there any way you could post a zip instead of rar? I can't view rar's.


----------



## AJExcel (Oct 13, 2008)

Hello MRdNk,

First, hope you had a good weekend. I know you been working on building this worksheet, and I appreciate your efforts. I was wondering if I could be of any help to you in resolving the issues. Please keep me posted.


Thanks,

AJ


----------



## AJExcel (Oct 13, 2008)

Zack,

I appreciate, you coming forward to help me and MRdNk in completing this workbook. Please let me know if I could be of any help to you.

Thanks a lot,

AJ


----------



## MRdNk (Apr 7, 2007)

Hey, I've been having a few computer problems, give me a few days and I'll get something to you.


----------

