Viewing 24 posts - 1 through 24 (of 24 total)
  • Any Excel guru's in the house? Macro help
  • chilled76
    Free Member

    Hello,

    Trying to make a macro that if I select a load of cells, after I’ve clicked print I can run the macro and it automaticall hide the bottom row of what was selected and then selct all the former cells and the new bottom row that has moved up in place of the hidden one.

    Is this possible?

    Any help would be most appreciated.

    footflaps
    Full Member

    Yep very possible.

    You need to use VBA to do so, Alt-F11 for VBA editor.

    Find the “This Workbook” module in the Project Explorer and add to it:

    Private Sub Workbook_BeforePrint(Cancel As Boolean)

    End Sub

    This is a macro which will be called before print.

    You then need to frig around with the current selection eg the macro below will tell you how many rows and cols you have selected to print and will then cancel printing (as it’s just a test).

    Private Sub Workbook_BeforePrint(Cancel As Boolean)

    MsgBox (“You have selected ” & Selection.rows.Count & ” rows and ” & Selection.Columns.Count & ” cols”)

    Cancel = True

    End Sub

    eg Ahhh They’ve broken Flickr yet again, so I can’t get BB code for it!

    chilled76
    Free Member

    Sorry, that is french to me. But thank you very much for responding.

    Let me try re-explaining as well as I don’t think my op was that clear.

    Basically diregard the print…

    What the macro needs to do is hide the bottom row of what I have selected but re-select all the cells originally selected and the new bottom row that is now sitting in place of the one that got hidden.

    Is that still something I can’t do with a record macro and need to programme?

    Fresh Goods Friday 696: The Middling Edition

    Fresh Goods Friday 696: The Middlin...
    Latest Singletrack Videos
    apj
    Free Member

    Chilled

    Don’t think recording will work, as by default it will record the row number of the last row when you record it, rather than the fact that it is the last row. So it will need some programming, which footflaps appears to be (very kindly) trying to help with / do for you, and even tells you which buttons to press to get into the VBA editor! If he doesn’t come back I will try and help as well. Couple of questions though:

    Do you want to just hide the row (it can be unhidden later), so that it for example your selection ends in row 71,72,73 then the macro hides row 73 and changes the selection to that the rown number on the left read 71,72,74? Or do you actually want to delete row 73, so that the old row 74 is now row 73.

    Taking a step back, what is it you are trying to achieve by doing this, as there may be a better way of doing it than simply macro-ifying a cumbersome manual process?

    chilled76
    Free Member

    I want it to just hide the row.

    I am very grateful for both your reponses, so thank you and please be patient if I seem thick, I’m not I’m just new to macro’s and programming.

    Backround… I’m head of maths in a school (no I’m not striking, but I’ve got no kids in today to teach as lots of people are).

    I have spreadsheets which have details on pupils weak areas. Topics and revision references across the top.

    Pupils names down the left with lots of individualised data as youp read across for each pupil.

    Normally when I provide this info to my pupils I select everything across the top and just the first pupil. I then click Ctrl p and send this to the printer (set the printer up to print selection and scale to page).

    I then hide the first pupil and repeat the select and print for the next pupil, then hiding them and printing the next pupil and so on. Once I’ve set the printer after the first one it is set until I close the document so I only have to do this once.

    About half an hour later, all of our year 11’s have individualised target areas with referenced revision material… but the printing is a right ball ache.

    If I had a macro I could literally press ctrlp return ctrl and whatever key the macro is set up to over and over until they are all done. Would take 90 seconds to print all of them.

    Any help greatly appreciated as I have today to get this sorted.

    chilled76
    Free Member

    Oh and the selection idea means that I can choose to be using as little or as many of the target areas I want and utilise it on different spreadsheets that may span different widths etc.

    apj
    Free Member

    Sorry, last post was a bit snippy!

    The following code does the following which (I think!) is a better way of doing it:

    1: Sets the first row of the sheet as the titles to print on each page, and sets printing to “fit to page”.
    2: Goes through each row of your selection, sets that as the print area, and print it with the titles

    To get the code below into a new macro, go Macros->Record Macro, type “printeachrow” as the macro name, click on create , then cut and paste the code below between the “Sub printeachrow()” and “End Sub” lines. Then save and exit the VBA editor. Then assign a Ctr-key combo as you would for a recorded macro.

    IMPORTANT: try this on a small selection first, to avoid paper-wastage.

    ‘This is a comment

    ‘ Setting the header rows to print at the top
    ‘ This assumes you just want the top row
    ‘ If you want e.g. the top 2 change it to “$1:$2”
    With ActiveSheet.PageSetup
    .PrintTitleRows = “$1:$1”
    .FitToPagesWide = 1
    .FitToPagesTall = 1

    End With

    ‘ Now we go through each row of your selection, set the
    ‘ print areas as that row, and print it.
    For Each rw In Selection.Rows
    ActiveSheet.PageSetup.PrintArea = rw.Address

    ‘ But don’t print the header row!
    If rw.Row > 1 Then ActiveSheet.PrintOut

    Next rw

    apj
    Free Member

    P.S. MS guidance for making macros globally available (so you don’t have to set the macro up in each workbook):

    [/url]

    That guidance assumes you record your macros, so you could just record a blank macro then edit the macro paste in the code above.

    Hope this helps.

    robbo
    Free Member

    Chilled, I have a template that does exactly what you are talking about. For Maths teachers too!

    Email me (see my profile) and I’ll send you a copy!

    jfletch
    Free Member

    Can you use subtotals?

    You can select all the data and press subtotal (on the “data” ribbon)

    You can define where you want your subtotals, for you this would be after each pupil. You can also select to put a page break after each group. Tick this. Then when you apply the subtotal it will put a page break after each pupil’s name.

    Would cut down the 30 mins into about 2 if you do it this way.

    It could be even more slick if you used a macro and the code would be fairly simple but you would still need to code it. Doesn’t seem worth the hassle to spend hours fidling with code to cut a 2 min job down to a 1 min job.

    chilled76
    Free Member

    apj, that’s brilliant thanks.

    I’ve nearly got it working.

    Ive created a macro and edited you lines of code in.

    It’s actually the top four rows that need to be constant and present on everyone’s feedback… so I’ve edited two parts of your text and it is now as below.

    The problem is it seems to print a load of just the header pages as well as the pupil print outs and it’s hiding the 4th row too. Is this because the rows start at 2 so i should have used 5?

    In fact I’ll try

    ____________________________________________________________________

    Sub printeachrow()

    ‘ printeachrow Macro

    ‘ Keyboard Shortcut: Ctrl+y
    ”This is a comment

    ‘ Setting the header rows to print at the top
    ‘ This assumes you just want the top row
    ‘ If you want e.g. the top 2 change it to “$1:$2”
    With ActiveSheet.PageSetup
    .PrintTitleRows = “$1:$4”
    .FitToPagesWide = 1
    .FitToPagesTall = 1

    End With

    ‘ Now we go through each row of your selection, set the
    ‘ print areas as that row, and print it.
    For Each rw In Selection.Rows
    ActiveSheet.PageSetup.PrintArea = rw.Address

    ‘ But don’t print the header row!
    If rw.Row > 4 Then ActiveSheet.PrintOut

    Next rw

    End Sub

    crmcc
    Free Member

    This sounds like a perfect job for mail merge. Data in the spreadsheet used to create individual documents, first time takes a little setting up but is easy to modify for each student.

    chilled76
    Free Member

    I use mail merges all the time. This has a load of conditional formatting which can’t be exported. Targets in red amber green etc depending on target levels and what they’ve achieved.

    chilled76
    Free Member

    apj… I’ve got this to work. Thank you so much for your time. Really very appreciated.

    Thanks to everyone else that has chipped in too. This has saved me hours for the future.

    leffeboy
    Full Member

    You can do conditional formatting in Word if you like. You just need to use an IF/THEN and format the text accordingly

    chilled76
    Free Member

    I’m aware you can use conditional formatting in other microsoft products but you can’t export it in a mail merge if you’ve got data in a spreadsheet that is coloured against target data etc

    leffeboy
    Full Member

    Yep, but you can export the numbers in the mail merge and the do IF [Target]<[Actual] THEN RedText ELSE GreenText for example, or at least I think you can. I use it for bold text when I don’t have tax info. for people so it should also work for colours. You could also do the calculation in excel and just export that field e.g. [NotMetTarget] and then format based on that.

    Edit: Just tried it and it works (in Word 2010). There is a trick though. You need to use the ‘Rules’ part to insert the IF/THEN but it won’t let you change the colours or insert the MergeFields that you want. So once you’ve got it basically set up you press Alt-F9 so you can see the full merge stuff and then just type in the merge fields you want and change the colours/bold etc.

    apj
    Free Member

    You’re very welcome chilled, glad you got it working!

    “Proper” programmers often don’t like Excel/VBA, and you can end up making a monster with it, when you should be using a proper database, but for this sort of thing I think it’s quite cool. Also quite easy to teach yourself by recording something and looking at the code, plus a bit of googling / online help for syntax etc.

    footflaps
    Full Member

    “Proper” programmers often don’t like Excel/VBA, and you can end up making a monster with it,

    My current App is > 50k lines of VBA.

    Have to say I find VBA very stable, have very few problems with it even though I’m having it thrash the PC to death computation / disc access wise.

    jfletch
    Free Member

    My current App is > 50k lines of VBA.

    I’d hate to be your company if you leave. VBA consultants are thin on the ground/expensive and having to wade through 50k lines of code to find where someone has put the number in the wrong cell will be a nightmare.

    I love VBA as I know how to write it and a lot of people here now use Macros I have written. But I hate to think what will happen if/when I leave.

    footflaps
    Full Member

    I’d hate to be your company if you leave. VBA consultants are thin on the ground/expensive and having to wade through 50k lines of code to find where someone has put the number in the wrong cell will be a nightmare.

    I don’t put numbers or formulas in cells. They’re a nightmare to debug. You put all the intelligence in well documented OO code inside VBA and just use the WS for input and output. Customers cut and paste data into a WS, it gets sucked into VBA and processed and then a formatted output is written back out to a different WS. Since all our customers process everything in Excel it’s handy to use it as the front end as they’re familiar with it, but there’s no logic in the Worksheets themselves.

    If and when I leave the bigger issue is not the code, but the fact that no one else is familiar with the methods as they’re all pretty unique and my ‘brain child’. They’re also way ahead of any other commercial tools available, so key company IPR right now.

    soma_rich
    Free Member

    VBA consultants are thin on the ground/expensive

    Really? I have never seen a job asking for just VBA. I do quite a bit in it but only as most people like Excel.

    jfletch
    Free Member

    If and when I leave the bigger issue is not the code, but the fact that no one else is familiar with the methods as they’re all pretty unique and my ‘brain child’.

    Do you have any children you want to send to private school? I’d ask for a raise!

    footflaps
    Full Member

    Do you have any children you want to send to private school? I’d ask for a raise!

    It’s paying for both our cats to go to Oxbridge right now.

    Here’s George in his college tie:

Viewing 24 posts - 1 through 24 (of 24 total)

The topic ‘Any Excel guru's in the house? Macro help’ is closed to new replies.