Macro to print record # to record#

[Naomi_Oz]

Hi,

I'm trying to find a macro that will run allowing the user to select a 'starting record number' and a 'finishing record number' when printing.

I have a spreadsheet that feeds from a master list in excel, from over 5000 records.

I need to print the s'sheet with any given indivdual record's information at any given time.

Individual prints are fine. However if I wanted to print from record number 1500 to record number 3000 it would take me all day.

Is there a way I can set up a macro so an option form pops up? allowing selection of "From record" and "to Record" ?

Thanks in advance.
Naomi

 

[Greg Truby]

It is unlikely that you are simply going to stumble upon a macro that is going to do what you want. Anything you find is certainly going to entail at least some degree of customization. How much (if any) VBA do you know?

Are you simply wanting to start printing the spreadsheet's contents at row X and stop at row Y? That's pretty simple, just resize the print range.

But you say "any given indivdual record's information at any given time" which seems to imply that you are taking one record and populating some type of "report" or custom layout and printing one record on one page. If that's the case then this is a whole other kettle of fish.

Also, sometimes it doesn't matter what version of Excel you're running. But sometimes it does. Wouldn't hurt to let us know.

 

[Naomi_Oz]

Greg,
I see that I really wasn't very clear - but certainly very general. Sorry.

Firstly, I don't know a great deal about VBA at all.
Usually when I know what I want but can't figure it out I trawl the web for something similar. Paste into my doc and play around till I get it right. This has worked well for me with formulas etc because I learn as I go.

My HQ designed a "report" that uses simply Vlookups to pull data from the data page. There are buttons for record up and record down using VBA. If I want to print a single records populated report I can. Printing a Large selection of records will be very time consuming.
I can add a macro the the form, but everything else is protected.

I have my own version of the same report using excel data and word merge doc. This allows the same thing effectively, and I can print any record single selection or group. However, HQ prefers this format. :-/

I am currently using Excel 2003.

I hope I have made this a little clearer. Thanks for taking the time to respond. Appreciate it.

 

[Greg Truby]

How do you jump to record 1500? Surely you don't have to click the "record down" button 1500 times?

 

[Naomi_Oz]

I can key into that cell. Only that cell.
Tks
Naomi

 

[Greg Truby]

OK, I am totally spoonfeeding you this code. Normally I would make you learn by doing. What I'm thinking is that we might be able to end-run trying to hook into the previous/next buttons' code by simply inputting values into that jump cell and printing. Try playing with this

Sub foo()
    Dim lngStart&, lngEnd&, i&
    Let lngStart = InputBox("Start?")
    Let lngEnd = InputBox("Stop?")
    
    If lngEnd - lngStart > 500 Then
        If vbNo = MsgBox("Seriously?!  You want to print that many?", vbQuestion + vbYesNo, "Confirm") Then
            Exit Sub
        Else
            MsgBox "Hope the printer holds more than one ream of paper...", vbInformation
        End If
    End If
        
    For i = lngStart To lngEnd
        Sheets("whateversheetyoukeythisinon").Range("whatevercellyoukeythisinon") = i
        Sheets("whateversheetyouwanttoprint").PrintOut
    Next i
End Sub

 

[Naomi_Oz]

Thanks for 'spoonfeeding' me... I will play around with this and see how I go. Might even learn something

Thanks Again