Attempting to write a VBA code for Report Automation

Question

Hello all.
I am trying to program a couple of things inside of an Excel sheet.

The first item that I am attempting to do is to have rows automatically hide when another cell (which is not a part of the table).
To specify, I want to hide rows that have times outside of a certain shift (4 different options, 'M', 'D', 'A', 'N'). Each have different hours.

Using the above as an example: If H3 = N
Rows 12 -> 23 hide automatically. H3 is currently a drop down list to only choose the 4 different shift letters (with conditional formatting to change the cell fill color based on choice).

The second is a little more tricky (I think, it's all tricky for someone like me who is absolutely fresh/new to vba).
The 'Click to Submit' button is programmed to automatically send the file (as it displays) to the default e-mail program with the intended audience already addressed. What I would like to add to this function (which I've seen happen before) is for this submission button to also save as a local file with a pre-determined title (I'd like the file to save with the date and time as the button is pressed). Below is the current coding which has been written for the button (with e-mail addresses covered for privacy reasons).

Thank you in advance for any help in the right direction, and I hope you are all well!

Answer 1

Thank you for the code, but it doesn't seem to be doing anything. I've tried to edit it a little (using $ on targets) to make it a little more precise, and still nothing happens.

This is how it should look like

You don't need to edit anything

The macro should perfectly work

Share a link to the workbook so we could analyze it on our side

If you need help with how to upload the file please, click the link below

https://support.office.com/en-us/article/share-onedrive-files-and-folders-9fcc2f7d-de0c-4cec-93b0-a82024800c07

You may also follow the instructions in this video

https://www.youtube.com/watch?v=NnXsE0SNuCc&t=14s

Answer 2

I think Jeovany CV is right.

if still not work,please share your excel file.

I would like to run a macro with shortcut or a button instead of worksheet_selection_change.

add activesheet after if condition.

Private Sub macro1()

If activesheet.Range("h3").Value = "N" Then

ActiveSheet.Rows("12:23").Hidden = True

Else

If activesheet.Range("h3").Value = "A" Then

ActiveSheet.Rows("6:19").Hidden = True

Else

If activesheet.Range("h3").Value = "D" Then

ActiveSheet.Rows("6:11").Hidden = True

ActiveSheet.Rows("24:29").Hidden = True

Else

If Range("h3").Value = "M" Then

ActiveSheet.Rows("6:9").Hidden = True

ActiveSheet.Rows("20:29").Hidden = True

End Sub

Answer 3

Unfortunately, due to my companies privacy policies, I am unable to send it.

I managed to work around my issue by just programming more buttons to press to complete the row hiding (as seen below);

Private Sub CommandButton2_Click()

ActiveSheet.Rows("12:23").Hidden = True

ActiveSheet.Rows("6:11").Hidden = False

ActiveSheet.Rows("24:29").Hidden = False

End Sub

Private Sub CommandButton3_Click()

ActiveSheet.Rows("6:11").Hidden = True

ActiveSheet.Rows("24:29").Hidden = True

ActiveSheet.Rows("12:23").Hidden = False

End Sub

Private Sub CommandButton4_Click()

ActiveSheet.Rows("6:9").Hidden = True

ActiveSheet.Rows("20:29").Hidden = True

ActiveSheet.Rows("10:19").Hidden = False

End Sub

Private Sub CommandButton5_Click()

ActiveSheet.Rows("6:9").Hidden = False

ActiveSheet.Rows("20:29").Hidden = False

ActiveSheet.Rows("10:19").Hidden = True

End Sub

Private Sub CommandButton6_Click()

ActiveSheet.Rows("1:29").Hidden = False

End Sub

Thank you for the help with giving me something to work based off of. Now my only other task that I want to do is try to program the command1 button to save as the date and time prior to sending it via e-mail.