Hi all, I've got a macro that runs and every time a new sheet is run a dialogue box comes up saying '.... template is already open. Are you sure to want to open.' and I have to manually click yes for all.
How do I make my macro say yes to all and/or ignore the dialogue box? This is my code
How do I make my macro say yes to all and/or ignore the dialogue box? This is my code
Code:
'==========>>
Option Explicit
'---------->>
Public Sub PassVariables()
Dim WB As Workbook
Dim SH As Worksheet
Set WB = ThisWorkbook
Set SH = WB.Sheets("Sheet1")
Dim i As Variant
For i = 2 To 151
With SH
Call Main(myYear:=.Range("A2").Value, _
myQuarter:=CStr(.Range("B2").Value), _
myFolder:=CStr(.Range("C2").Value), _
mySaveAsFolder:=CStr(.Range("D" & i).Value), _
mySaveAsName:=CStr(.Range("E" & i).Value), _
blCreateFolder:=CStr(.Range("F" & i).Value))
End With
Next
End Sub '---------->>
'---------->>
Public Sub Main(myYear As Variant, myQuarter As String, _
myFolder As String, _
mySaveAsFolder As String, _
mySaveAsName As String, _
Optional blCreateFolder As Boolean)
Dim WB As Workbook
Dim WS As Worksheet
Dim spath As String
Dim sSaveAsPath As String
Dim sFilename As String
Dim sFullname As String
Dim aStr As String
aStr = myQuarter & " " & myYear
spath = "X:\SPECIFICFOLDER\" & myYear & "\" & aStr & "\TMT\" & myFolder
sSaveAsPath = "X:\SPECIFICFOLDER\" & myYear & "\" & aStr & "\TMT\" & mySaveAsFolder
sFilename = "ST" & aStr & ".xlsm"
sFullname = spath & "\" & sFilename
Workbooks.Open Filename:=sFullname, UpdateLinks:=0
ActiveCell.Offset(-1, 0).FormulaR1C1 = mySaveAsName
Set WS = ActiveSheet
Set WB = Workbooks.Add(xlWBATWorksheet)
WS.Range("A1:S84").Copy
WB.Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteValues
WB.Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
If blCreateFolder Then
MkDir sSaveAsPath
blCreateFolder = False
End If
'ChDir sSaveAsPath
With ActiveWorkbook
.SaveAs Filename:=sSaveAsPath & "\" & mySaveAsName, _
FileFormat:=xlOpenXMLWorkbook, _
CreateBackup:=False
.Close SaveChanges:=False
End With
End Sub
'<<==========