Hi
The code below works as a macro but I want the code in a command button on the worksheet instead. I am getting the error 'Method Range of object Worksheet failed' error message at the line: What am I doing wrong? All assistance greatly appreciated.
The code below works as a macro but I want the code in a command button on the worksheet instead. I am getting the error 'Method Range of object Worksheet failed' error message at the line: What am I doing wrong? All assistance greatly appreciated.
Code:
For Each rngdept In ws.Range(strvalidation).Cells
VBA Code:
Private Sub CommandButton1_Click()
Dim strvalidation As String
Dim rngdept As Range
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim strPath As String
Dim site_name As String
Dim msgname As String
Set ws = Sheets("Dept")
Set ws2 = Sheets("General")
site_name = ws.Range("B2").Value
msgname = site_name & " departments printed"
strPath = ws2.Range("B7").Value
Application.ScreenUpdating = False
ws.PageSetup.PrintArea = ""
strvalidation = ws.Range("B4").Validation.Formula1
For Each rngdept In ws.Range(strvalidation).Cells
ws.Range("B4").Value = rngdept.Value
If ws.Range("B4").Value = "Emergency Department" Then
ws.PageSetup.PrintArea = "B5:O36"
Else: ws.PageSetup.PrintArea = "B5:O65"
End If
ws.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=strPath & "Patient Safety_2019_20_" & site_name & "_" & rngdept & ".pdf", _
Quality:=xlQualityStandard, _
includeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Next rngdept
Application.ScreenUpdating = True
MsgBox msgname, vbOKOnly
End Sub