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.

auto insert a formula

Discussion in 'Business Applications' started by arrrgh2003, Apr 1, 2004.

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

    arrrgh2003 Thread Starter

    Joined:
    Dec 4, 2003
    Messages:
    83
    I have formulas copied down to 500 rows but only have data up to 50 rows just now, if i print then I get 14 blank pages. I know I can select to print one page

    can I enter a command or formula in say: B5 that says IF there is an entry in A5 THEN copy the formulas from ( B4:D4)

    That would mean (in my head!) every time I entered a date in A# then the formulas would be copied down from the above row so the spreadsheet would only extend as I enter data?

    Possible? hope so

    Thanks
     
  2. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> I know I can select to print one page.

    JFI, there's also Print Selection. So you could opt for this having (a) selected A1 (b) (while holding down SHIFT) pressed

    END - DOWN - END - RIGHT.

    Of course, this doesn't cover the "copy/create formulas on the fly" aspect. For which you could always use VBA. But before we go there, consider an often overlooked ?feature? in XL2K (and later, probably) under Tools -- Options -- Edit ; "Extend Formats and Formulas".

    Although the screen-tip help for this says you need the formulas in >=3 of the previous 5 rows, I actually need it in 4 to get it going.

    The only thing you may need to watch is it seems to function at application level ; i.e. when turned on, it'll be on for all open workbooks.

    HTH,
    Andy
     
  3. arrrgh2003

    arrrgh2003 Thread Starter

    Joined:
    Dec 4, 2003
    Messages:
    83
    Hi Andy
    I only have 2 formulas in a total of 11 columns so I think the 'extend...' option wont work.

    I've never used VBA (is that visual basic?) so any pointers to a website for tutorials would be helpful. I'll search in the meantime.

    If I use VBA can I save it with the workbook so it will be seamless to others using it?
    thanks
     
  4. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> copy the formulas from (b4:d4)

    That's three columns, yet

    >> I only have 2 formulas in a total of 11 columns

    ???

    >> I think the 'extend...' option wont work.

    Sure it will. But since you now advise that 'users' will be involved, they would have to watch the "Extend" option, so let's not go there.

    CTRL+F3. Define a name "Print" with the formula

    =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),11)

    (adjust to cover whatever your sheet is called. NB - in the "Refers To" box you'll need to F2 for Edit mode).

    Rightclick the XL icon to the left of File in the menu bar and choose View Code.

    This'll get you to the workbook module. Paste this in ;

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    ActiveSheet.PageSetup.PrintArea = "Print"
    End Sub

    This will reset the print area before printing to the dynamically named range "Print" (which is equivalent to # of filled rows in column A x 11 columns).

    Rgds,
    Andy
     
  5. arrrgh2003

    arrrgh2003 Thread Starter

    Joined:
    Dec 4, 2003
    Messages:
    83
    cheers Andy
    Not sure the wife'll be happy being refered to as 'users' lol

    I only used those cells as an example, sorry, the formulas are in H3 and K3.

    I'll go through the rest of the post and do the printing as you decribed as that sounds ideal
     
  6. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> Not sure the wife ...

    Sounds like you'll be OK. I get grief just for referring to my significant other as "the wife". :D

    Post back if you get stuck. Best rgds,
    Andy
     
  7. arrrgh2003

    arrrgh2003 Thread Starter

    Joined:
    Dec 4, 2003
    Messages:
    83
    Hi Andy
    That works perfectly on the first sheet but now I have another problem. I need a copy of the full sheet for myself and a copy of it with one column removed for the client as it holds personal info on them.
    Here's what I tried-
    I have copied the entire sheet using =sheet1!## in all cells but omitted the one column which has private information. I need this to be printed in the same way but I find that the VBA references are all pointing to the first sheet or in fact to the whole workbook.

    Can I do it like this with some changes to the code? I tried a few changes myself but none worked.
     
  8. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> a copy of it with one column removed

    >> I have copied the entire sheet using ...

    Nah, you lost me. The code applies to all sheets since it's in the workbook module. You can't make the Workbook_BeforePrint event sheet-specific (unless you throw in something like if sheet.name <> "blah" then exit sub.

    If you're just talking hard copy, what happens if you hide the column in question before print?

    Rgds,
    Andy
     
  9. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> Nah, you lost me.

    Looks like it's mutual, which tends to happen ; must work on my technique.

    Maybe you should opt for a button on the sheet itself, with code in a regular module attached to it.

    Sub Print2_inc_client()
    ActiveSheet.PageSetup.PrintArea = "Print"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Columns("d:d").EntireColumn.Hidden = True
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Columns("d:d").EntireColumn.Hidden = False
    ActiveSheet.PageSetup.PrintArea = ""
    End Sub

    Rgds,
    Andy
     
  10. arrrgh2003

    arrrgh2003 Thread Starter

    Joined:
    Dec 4, 2003
    Messages:
    83
    Thats what I am looking at now, you saved me a lot of time figuring out code
    Cheers
     
  11. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    OK, good stuff.

    I'll be off now cos Gram's here (he's a Toon man & the wife follows Liverpool).

    If you get stuck he'll put you straight.

    Best rgds,
    Andy
     
  12. 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!

Loading...
Thread Status:
Not open for further replies.

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

  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