How to clone a form with vba behind it, without getting into trouble?

[TomYC]

Let me explain!
I have an Excel form, with a command button that triggers some action, basically picking up values in a few cells and using that to create a file name, then outputting a pdf of the form.
When I cloned this form/worksheet, I of course had to rename the worksheet copy, but then the vba didn't work, as it was referencing (using the RANGE() function) the other worksheet. So modified the code to match the new worksheet and...well, the original worksheet now didn't work because its code had changed.
So what is the best practice in this?
Below is my code:
Private Sub CommandButton1_Click()
If Me.ListBox1.ListIndex <> 0 Then
Me.ListBox1.ListIndex = 0
'MsgBox "Not at top of listbox: " & Me.ListBox1.ListIndex
End If

Dim myDir As String, fname As String, FullName As String, stDate As String
Dim X As Integer


For X = 0 To ListBox1.ListCount - 1


stDate = Format(Range("'Dashboard-Director_PDF_RO'!G6"), "MMM-YYYY")
'MsgBox "Date String is " & stDate

myDir = "\\nt2kmh300srv02\sharedata\staffingeffect\HR_Scorecard\Reports\"
If Dir(myDir, vbDirectory) = "" Then
MsgBox "Wrong folder path"
Exit Sub
End If
If Range("'Dashboard-Director_PDF_RO'!A2") = "#N/A" Then
Exit Sub
End If
fname = "HR Scorecard -- " & Range("'Dashboard-Director_PDF_RO'!D2") & " - " & Range("'Dashboard-Director_PDF_RO'!A2") & " -- " & Range("'Dashboard-Director_PDF_RO'!C2") & " - " & stDate & ".pdf"
FullName = myDir & fname
'MsgBox "Active Workbook is " & ActiveWorkbook.Name
'MsgBox "FullName is " & FullName

Application.DisplayAlerts = False
'ActiveWorkbook.FollowHyperlink fpath & attach_name, NewWindow:=True

ActiveWorkbook.FollowHyperlink FullName, NewWindow:=True


'ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FullName _
, Quality:=xlQualityMedium, IncludeDocProperties:=False, _
IgnorePrintAreas:=False, OpenAfterPublish:=True


Application.DisplayAlerts = True
If Me.ListBox1.ListCount - Me.ListBox1.ListIndex = 1 Then
Me.ListBox1.ListIndex = 0
Exit Sub
Else
Me.ListBox1.ListIndex = Me.ListBox1.ListIndex + 1
End If

If Err.Number = 7777 Then
'End of the list - Move to the start.
Me.ListBox1.ListIndex = 0
Exit Sub
Else
'MsgBox Err.Description, vbOKOnly, "Error #" & Err.Number & "Occurred"
End If
Next X



End Sub

 

[SkipVought]

hi,

you use this range several places

If Range("'Dashboard-Director_PDF_RO'!A2")

You might rather do this...

with ActiveSheet
   If .Range("A2") = "#N/A" Then  Exit Sub

   fname = "HR Scorecard -- " & .Range("D2") & " - " & .Range("A2") & " -- " & .Range("C2") & " - " &   
     stDate & ".pdf"
end with

the DOT before range references the With ... End With statement in which it immediately resides.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]