Posts by H123

Quote from Carim

You could test the following

Works perfectly - thanks again Carim!

Hi gurus

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?

Thanks

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

Wo

Quote from Carim

You could test the following

Worked like a charm Carim - thanks again

Hi all

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

Sub Button2_Click()

Sheets("Oil Record Book Sheets").Select

Range("A93:I184").Select

Selection.PrintOut Copies:=1, Collate:=True

Sheets("Worksheet").Select

Range("R36").Select

ActiveCell.FormulaR1C1 = "=NOW()"

Range("R37").Select

End Sub

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.

Quote from royUK

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

Cheers

Hey Carim

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