auto insert a formula

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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

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

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

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

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
 
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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Members online

Top