Message to replace contents

[FrankB]

I have a VBA macro that is copying the MASTER file to 15
other workbooks. Everything works fine with the exception
of getting the message "Do You Want To Replace The
Contents of the Destination cells". What code can I add
into my macro to just accept this process and not show the
message.

Thanks
Frank

 

[FrankB]

-----Original Message-----
Frank,

What code are you using that generates this error?

--

HTH

Bob Phillips




.

Bob is isn't an errror message it's just a message that
asked if I want to save it or not over an existing file
with the same name. What I would like to put in my code
is a statement so it by passes this message and saves it
automatically.

Thanks
Frank

Workbooks.Open Filename:="G:\04Plan\Cap Models\Cap

Model.xls"
ChDir "G:\04Plan\Cap Models\Project Managers"
Range("b2").Select
ActiveCell.FormulaR1C1 = "1040"
ActiveWorkbook.SaveAs Filename:= _
"G:\04Plan\Cap Models\Project Managers\1040 Cap
Model.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close

 

[Bob Phillips]

Frank,

Sorry didn't mean to call it an error.

Try this, simplified code. I have removed the double save as well.

Workbooks.Open Filename:="G:\04Plan\Cap Models\Cap Model.xls "
ChDir "G:\04Plan\Cap Models\Project Managers"
Range("B2").FormulaR1C1 = "1040"
Range("A1").Select
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"G:\04Plan\Cap Models\Project Managers\1040 Cap Model.xls", _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Close
Application.DisplayAlerts = True

 

[FrankB]

-----Original Message-----
Frank,

Sorry didn't mean to call it an error.

Try this, simplified code. I have removed the double save as well.

Workbooks.Open Filename:="G:\04Plan\Cap Models\Cap Model.xls "
ChDir "G:\04Plan\Cap Models\Project Managers"
Range("B2").FormulaR1C1 = "1040"
Range("A1").Select
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"G:\04Plan\Cap Models\Project Managers\1040 Cap Model.xls", _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Close
Application.DisplayAlerts = True


--

HTH

Bob Phillips




.
Bob this works great, however now that I have this

portion working is there way to maybe put a message box to
ask for the password. My problem is that lets say that I
have opened 9 out of the 15 and on the tenth one I type in
the wrong password for that file, well it bounces me out
of the run mode for the VBA program. What I would like to
do is give them a SECOND change to input the correct
password and they the program would continue. Can Do???

Thanks for all you help

Frank

 

[Bob Phillips]

Frank,

This is untested, but give it a whirl and let me know if you encounter any
problems

Dim i As Long
Dim sPassword As String
Dim oWB As Workbook

For i = 1 To 2
sPassword = InputBox("Please supply password")
On Error Resume Next
Set oWB = Workbooks.Open(Filename:="G:\04Plan\Cap Models\Cap
Model.xls ", password:=sPassword)
On Error GoTo 0
If Not oWB Is Nothing Then
ChDir "G:\04Plan\Cap Models\Project Managers"
Range("B2").FormulaR1C1 = "1040"
Range("A1").Select
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"G:\04Plan\Cap Models\Project Managers\1040 Cap Model.xls",
_
FileFormat:=xlNormal, _
password:=sPassword, _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Close
Application.DisplayAlerts = True
Exit For
End If
Next i