I am currently using a macro (button) to select a range on another sheet and print out.
I would like to hide the sheet but still allow it to be printed - do I need to include unhiding and re-hiding the sheet as part of the print macro or is there a code that I can add to the macro (below) to enable printing of the target without unhiding the sheet?
- Sub Button01_Click()
- Sheets("Oil Record Book Sheets").Range("A1:I92").PrintOut Copies:=1, Collate:=True
- With Sheets("OB Discharge Records").Range("R18")
- .FormulaR1C1 = "=NOW()"
- .NumberFormat = "d/mm/yyyy hh:mm"
- .Value = .Value
- End With
- Sheets("OIW results").Select
- Range("A65536").End(xlUp).Offset(1, 0).Select
- End Sub
I have assigned a macro to a radio (print) button that will print a specific range (report) and insert a timestamp in a dedicated cell to indicate the date/ time when that report was printed (using the =NOW() function)
There are multiple buttons with the assigned macros and cell ranges updated respectively but, regardless of which button I press, all other timestamps update to reflect the time the most recent button was actioned.
If someone could review the code below and make a suggestion it would be appreciated
Sheets("Oil Record Book Sheets").Select
Selection.PrintOut Copies:=1, Collate:=True
ActiveCell.FormulaR1C1 = "=NOW()"
Thanks again Carim,
I went through the code and worked it out - very nice code, a lot prettier than anything I'd come up with.
It's much better to keep all data on one sheet, then build reports from that data
I agree but the workbook has 2 purposes for 2 different groups
1. to capture all data on sheet 1 and
2. separate all data that meets the 'yes' criteria and build a report based on that data only
Unfortunately the report has a specific layout and text that must be followed.
For this little amateur, it's easier format the report and cross reference cells as appropriate. That said, when I get to that stage I may come back and pick your brains for a smarter way
Thanks for the welcome and the prompt response - works perfectly
The 'lost' columns are to convert % to cubic metres (the source data only displays the % value)
If I was going to insert the two lost columns (and formula) between columns J/K and L/M respectively, would I have to adjust the code?
Cheers again for your assistance - it's awesome
Hi Excel gurus
I am looking for a code that will automatically copy an entire row from sheet 1 to sheet 2 (next available row) once the value 'Yes' in column H is selected from a drop down list
The workbook will be in constant use for many years to come, and will need to keep previous historical entries (no overwriting)
Sheet 2 will eventually be used to populate a third sheet where the values are laid in a report format but I only need the the auto population of sheet 2 at this stage
I'm pretty new to VBA and all efforts to use BBA code for similar issues have ended in failure so any guidance appreciated
I've attached a copy of the workbook if it helps