C
Casey
Hi,
I have a routine that copies a worksheet, opens the SaveAs dialog wit
a File name made up of values from named ranges and a date from th
copied sheet. It also attempts to make a new folder appended to th
current workbook path. Here's where I'm having trouble. I keep gettin
the run-time error 75. The problem is that the error is intermittent.
have tried moving the lines around thinking somehow the MkDir wa
picking up the newly copied sheet's path instead of the origina
workbook path, but no joy.
Here is the code:
Option Explicit
Private Sub cmdCopySaveAs_Click()
Dim c As Range
Dim d As Range
Dim NewSht As Worksheet
Dim rngDI As Date
Dim Fname As Variant
Dim str1 As Variant
Dim str2 As Variant
Dim str3 As Variant
str1 = Sheets("Proposal").Range("Lang1").Value
str2 = Sheets("Proposal").Range("Lang2").Value
str3 = Sheets("Proposal").Range("Lang3").Value
rngDI = Sheets("Proposal").Range("PropDate").Value
Fname = Sheets("Set-Up").Range("Project_Name").Value _
& " " & Sheets("Set-Up").Range("Contractor_s_Name").Value _
& Format(rngDI, " mm-dd-yyyy ")
MkDir ThisWorkbook.Path & "\Proposals" <<<<ERROR
On Error GoTo 0
Sheets("Proposal").Copy
Application.Dialogs(xlDialogSaveAs).Show ThisWorkbook.Path _
& "\Proposals\" & Fname & ".xls"
Set NewSht = ActiveSheet
On Error Resume Next
Application.ScreenUpdating = False
Application.EnableEvents = False
With ActiveSheet
.Shapes("cmdCopySaveAs").Delete
.Shapes("cmdPickScopes").Delete
.Range("Lang1") = str1
.Range("Lang2") = str2
.Range("Lang3") = str3
Set d
ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
For Each c In d
With c
.Value = .Value
End With
Next c
End With
ActiveWorkbook.Save
Application.EnableEvents = True
Application.ScreenUpdating = True
End Su
I have a routine that copies a worksheet, opens the SaveAs dialog wit
a File name made up of values from named ranges and a date from th
copied sheet. It also attempts to make a new folder appended to th
current workbook path. Here's where I'm having trouble. I keep gettin
the run-time error 75. The problem is that the error is intermittent.
have tried moving the lines around thinking somehow the MkDir wa
picking up the newly copied sheet's path instead of the origina
workbook path, but no joy.
Here is the code:
Option Explicit
Private Sub cmdCopySaveAs_Click()
Dim c As Range
Dim d As Range
Dim NewSht As Worksheet
Dim rngDI As Date
Dim Fname As Variant
Dim str1 As Variant
Dim str2 As Variant
Dim str3 As Variant
str1 = Sheets("Proposal").Range("Lang1").Value
str2 = Sheets("Proposal").Range("Lang2").Value
str3 = Sheets("Proposal").Range("Lang3").Value
rngDI = Sheets("Proposal").Range("PropDate").Value
Fname = Sheets("Set-Up").Range("Project_Name").Value _
& " " & Sheets("Set-Up").Range("Contractor_s_Name").Value _
& Format(rngDI, " mm-dd-yyyy ")
MkDir ThisWorkbook.Path & "\Proposals" <<<<ERROR
On Error GoTo 0
Sheets("Proposal").Copy
Application.Dialogs(xlDialogSaveAs).Show ThisWorkbook.Path _
& "\Proposals\" & Fname & ".xls"
Set NewSht = ActiveSheet
On Error Resume Next
Application.ScreenUpdating = False
Application.EnableEvents = False
With ActiveSheet
.Shapes("cmdCopySaveAs").Delete
.Shapes("cmdPickScopes").Delete
.Range("Lang1") = str1
.Range("Lang2") = str2
.Range("Lang3") = str3
Set d
ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
For Each c In d
With c
.Value = .Value
End With
Next c
End With
ActiveWorkbook.Save
Application.EnableEvents = True
Application.ScreenUpdating = True
End Su