Tech Support Guy banner
Status
Not open for further replies.
1 - 11 of 11 Posts

·
Registered
Joined
·
83 Posts
Discussion Starter · #1 ·
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
 

·
Registered
Joined
·
2,702 Posts
>> 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
 

·
Registered
Joined
·
83 Posts
Discussion Starter · #3 ·
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
 

·
Registered
Joined
·
2,702 Posts
>> 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
 

·
Registered
Joined
·
83 Posts
Discussion Starter · #5 ·
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
 

·
Registered
Joined
·
2,702 Posts
>> 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
 

·
Registered
Joined
·
83 Posts
Discussion Starter · #7 ·
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.
 

·
Registered
Joined
·
2,702 Posts
>> 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
 

·
Registered
Joined
·
2,702 Posts
>> 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
 

·
Registered
Joined
·
2,702 Posts
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
 
1 - 11 of 11 Posts
Status
Not open for further replies.
Top