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

If H3 = N

Rows 12 -> 23 hide automatically. H3 is currently a drop down list t

if range("h3").value="N" then

activesheet.rows("12:23").hidden=true

else if ....

end if

https://docs.microsoft.com/zh-cn/office/vba/api/excel.range.hidden

2,

first saveas

https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.saveas

then

attachments.add the_new_date_xlsx.xlsx

Answer 3

So if I'm reading the information for the first item correctly... I'd want something that would look like;

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

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

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

Else

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

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

Else

If 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 SubIs that close?

Answer 4

Hi Marshall

Very close, well done

You may try the one below

''''''''''*******************************************************************

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Application.Intersect(Target, Range("H3")) Is Nothing Then

            Me.Rows("6:29").Hidden = False   ''' To undo previous selection 

    Select Case Target.Value 

                Case Is = "N" 

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

                Case Is = "A" 

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

                Case Is = "D" 

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

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

                Case Is = "M" 

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

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

                Case Else 

                        Me.Rows("6:29").Hidden = False 

       End Select 

End If

End Sub

'''''''************************************************************************

Regards

Jeovany

Answer 5

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.