VBA Set Print Area???

[davidgottlieb]

Ok. I have a sheet where when the user chooses his/her name it advance filters to there detail. I also have a button that will print that detail. How can I get it to set the print area to how ever much detail is there because it differs from person to person. Let me know if you can grasp that. I know its kind of hard but let me know and I can try to explain it better. Thanks Guys and Girls

[davidgottlieb]

Ok. Well it seems like none of you are interested so let me break it down. You know how when you are recording a macro you can set it to Relative References or Absolute References. How can I set the print area setting the References to Relative and be able to set the print area to where the end of the detail is??? Come and help me out guys. Please!

[davidgottlieb]

PLEASE GOD HELP ME....SOMEBODY...DAMN...DOES NO ONE KNOW VBA/?????

[ctfreel]

I use VBA exclusively and have had the same situation as you. Try this...Once the filter has identified the data for a person, highlight a cell and do a "Select Current Region". That will highlight all the info for that person. Then you can set the selection as the print range.

Let me know if it works.

[davidgottlieb]

I'm not sure that I know what you mean. I am a beginner, could you please explain???


Thank you for actually replying. I can never get a response on here. LOL

[ctfreel]

I assume you have a filtered range with headers, etc. If you highlight any cell in that range, even the header, then Edit, GoTo, Special, Current Region it will select the entire range. Then you can set that selection as the print range.

Assume for example your filtered data begins in cell A1.
The code would look something like this:

Range("A1").Select
Selection.CurrentRegion.Select
ActiveSheet.PageSetup.PrintArea = Selection

[davidgottlieb]

Yeah but the print range is going to be different everytime. How can I set it to where it will select everything???

[ctfreel]

I understand. By using GoTo Current Region, it will select the data no matter how much data there is...whether there is one row or 200 rows. It selects the whole area. Then the code which sets the print range sets it to that area. Try it...I'm sure it works because I use it all the time in my macros.

[davidgottlieb]

So what do I set the print range as? The first cell, or the whole thing. The headers go from range A:AA. How much of that do I select....I'm just so damn confused. LOL....

[ctfreel]

THE CODE I SENT YOU SELECTS THE RANGE FOR YOU!! USE THE CODE, MAN!!

The code selects A1, the first cell in your range. Then the GoTo Current Region selects the ENTIRE RANGE as needed, no matter how SMALL OR BIG the range is. That is the code section that selects the code. The last line sets the print range to whatever is selected using the Current Region line above it. Put the code into a module and test it...I promise it WILL WORK!!!

[davidgottlieb]

Ok this is what my code is exactly how I have it...Tell me if its right or wrong.

Sub Print2()
    Range("A30").Select
    Selection.CurrentRegion.Select
    ActiveSheet.PageSetup.PrintArea = Selection
End Sub

If this is right then something is wrong. Its not working.
I'm sorry man. I am a real beginner...People like you will make me better.

Thanks

[ctfreel]

I'd be glad to help you figure this out but it will have to be Monday. Send me an email and I'll respond directly to you. If possible, send me part of the actual worksheet so I can look at how your data is arranged. Don't worry, I'm relatively new to VBA myself....

[davidgottlieb]

Where you from man. ?? Yeah I will send you the whole file....I'm sending now.