Adding data to all sheets at once, printing specific sheets (w/ formula) - help please.

Hello

Thanks for reading.

I am working on a document for my work at the moment. I can't upload the document because it includes account information so I'll try and describe it as best as I can. Skip down to jump straight to my problems.

The idea of this workbook is to speed up our invoice process. Our other offices (we are a franchise) will send us invoices and we have to take note of each invoice number and the amount and then at the end of the month we add it all up and send it. So I made this document to try and speed that up. The first sheet has the details of all of our other offices (name, account number, sort code, email address, address, post code etc etc). On this first sheet I have hidden 20 cells underneath each office. So row numbers are actually showing as 1, 2, 23, 44, 65, 86 etc. There are then 60~ sheets. 1 sheet per office. These sheets are drawing some of the account information from the front sheet - such as office name and account number and sort code. They also have a 20row table set out that are drawing their information from sheet 1. e.g. ='CONTACT DETAILS'!A3 through ='CONTACT DETAILS'!A22. The idea is that I can 'expand' (unhide) the 20 rows on the front sheet, type in whatever information I need to and it will transfer automatically to the appropriate sheet (we can and do get multiple invoices from each office each month). The idea being that I can then print that sheet off.

However, we don't get invoices from every office every month. So my first question is:

Can I make it so that excel will only print the sheets with data in a certain cell?
So, would I be able to tell excel that I only want to print the sheets that have a value in the 'total' cell (the cell that totals all each invoice). Basically, I don't want to bother printing any of the sheets that we haven't had an invoice for that month. i.e. total = £0.00.

Also

Can I add something to all (or specific) sheets at once?
For example, I would like to add a few notes about keyboard shortcuts that other employees can use but I would really like to add this to all of the sheets without having to add it to each individual sheet.

And finally

Is there any way to hide/unhide rows quickly?
As mentioned, I have the 20 rows underneath each office's details that are hidden. Is there any way to add, say, a button that would hide/unhide rows. I would be looking to add a button at the beginning of each data row that would hide and unhide the 20 below.


I hope I've made that reasonably coherent, thank you in advance.

David.

I have some buttons working now that show the 20 lines and hide them. But I am having to record each macro individually. Does anyone know how to program it so that it hides/shows the next 20?

Sub ashton_hide()
'
' ashton_hide Macro
'

'
Rows("3:22").Select
Selection.EntireRow.Hidden = True
End Sub


---------------------------------------------------------------------

Sub ashton_show()
'
' ashton_show Macro
'

'
Rows("2:23").Select
Selection.EntireRow.Hidden = False
Range("A3:B3").Select
End Sub




Rather than "Rows ("2:23")" I want it to be "Rows (next 20)".
:D

thanks again

You seem to have some grasp of VBA

1) Loop through the sheets and check the cell you need to test. If you need to print, use Sheet.Printout

2) This needs more definition. Easy way is to have an assigned cell on each sheet that just references the cell in your base sheet. If you need a macro, then it is certainly possible to loop through the sheets, but you would somehow need to tell it what is the source cell, and handle cases of overwriting data if needed.

3) Can you use Group to hide/unhide the rows? Highlight the rows to 'hide' and Shift-Alt-Right Arrow.

Pauley

Thank you very much.

For 1)

would you possibly be able to give me an example of what it would look like if:

I was testing cell A1 to see if it had value >0. If value >0 then Sheet.Printout

and how the loop might look

despite your kind words, I have extremely little coding experience.

Thanks once again

Here is an example which puts some text in cell A1 of each sheet:

Please Login or Register  to view this content.

Put this code in a Module for the workbook. That is, from the VBA code window, highlight your workbook, then Insert->Module if one does not already exist.

Pauley

The code above is more for your second question. Similar loop for the first question except you would have something like:

If Sheets(lcv).Range("A1") > 0 Then Sheets(lcv).Printout

inside the for loop.

Pauley

Thank you for your help. I'll have a play and see if I can get it working

you're a star!