the macro i am trying need to save as the file with name
"prevdayinventry.xls" and then need to clear the original workbook called
"inventry.xls".

macro called "newday" used to work to clear the page after promting the user
if they are sure they need to clear. now it stops runing after it
create/update "prevdayinventry.xls"

here is a extract from my macro:


Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "'ok' to clear page ... 'cancel' to go back" ' Define message.
Style = vbOKCancel ' Define buttons.
Title = "Maykent t/a KFC" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title)
If Response = vbOKCancel Then ' User chose Yes.


Sheets("DATA").Select

ChDir "C:\maykent\inventry"

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs
Filename:="C:\maykent\inventry\prevdayINVENTRY.xls ", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


Application.DisplayAlerts = True


Workbooks.Open Filename:= _
"C:\maykent\inventry\inventry.xls"

Windows("prevdayINVENTRY.xls").Activate
ActiveWindow.Close savechanges:=True



Windows("INVENTRY.xls").Activate

Sheets("data").Select

Range("b93").Select
Selection.ClearContents


Sheets("data capture").Select
Range("C1:C48").Select
Selection.ClearContents

You are closing the workbook that contains the macro. Of course it is going
to stop. You need to have a master workbook that opens and closes files or
don't close the workbook just save the data.

"pswanie" wrote:

the macro i am trying need to save as the file with name
"prevdayinventry.xls" and then need to clear the original workbook called
"inventry.xls".

macro called "newday" used to work to clear the page after promting the user
if they are sure they need to clear. now it stops runing after it
create/update "prevdayinventry.xls"

here is a extract from my macro:


Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "'ok' to clear page ... 'cancel' to go back" ' Define message.
Style = vbOKCancel ' Define buttons.
Title = "Maykent t/a KFC" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title)
If Response = vbOKCancel Then ' User chose Yes.


Sheets("DATA").Select

ChDir "C:\maykent\inventry"

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs
Filename:="C:\maykent\inventry\prevdayINVENTRY.xls ", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


Application.DisplayAlerts = True


Workbooks.Open Filename:= _
"C:\maykent\inventry\inventry.xls"

Windows("prevdayINVENTRY.xls").Activate
ActiveWindow.Close savechanges:=True



Windows("INVENTRY.xls").Activate

Sheets("data").Select

Range("b93").Select
Selection.ClearContents


Sheets("data capture").Select
Range("C1:C48").Select
Selection.ClearContents

any sugestions on how to edit the code to get a "backup" of the workbook
writen in to the code just after the user choose yes to clear the workbook
but before clearing.

my gues would be that by save as i interupt the code. but whats my
alternatives?


"Joel" wrote:

You are closing the workbook that contains the macro. Of course it is going
to stop. You need to have a master workbook that opens and closes files or
don't close the workbook just save the data.

"pswanie" wrote:

the macro i am trying need to save as the file with name
"prevdayinventry.xls" and then need to clear the original workbook called
"inventry.xls".

macro called "newday" used to work to clear the page after promting the user
if they are sure they need to clear. now it stops runing after it
create/update "prevdayinventry.xls"

here is a extract from my macro:


Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "'ok' to clear page ... 'cancel' to go back" ' Define message.
Style = vbOKCancel ' Define buttons.
Title = "Maykent t/a KFC" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title)
If Response = vbOKCancel Then ' User chose Yes.


Sheets("DATA").Select

ChDir "C:\maykent\inventry"

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs
Filename:="C:\maykent\inventry\prevdayINVENTRY.xls ", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


Application.DisplayAlerts = True


Workbooks.Open Filename:= _
"C:\maykent\inventry\inventry.xls"

Windows("prevdayINVENTRY.xls").Activate
ActiveWindow.Close savechanges:=True



Windows("INVENTRY.xls").Activate

Sheets("data").Select

Range("b93").Select
Selection.ClearContents


Sheets("data capture").Select
Range("C1:C48").Select
Selection.ClearContents

SAve AS a different filename which you are doing. Just don't close the
workbook before processing the Inventry.xls workbook.

"pswanie" wrote:

any sugestions on how to edit the code to get a "backup" of the workbook
writen in to the code just after the user choose yes to clear the workbook
but before clearing.

my gues would be that by save as i interupt the code. but whats my
alternatives?


"Joel" wrote:

You are closing the workbook that contains the macro. Of course it is going
to stop. You need to have a master workbook that opens and closes files or
don't close the workbook just save the data.

"pswanie" wrote:

the macro i am trying need to save as the file with name
"prevdayinventry.xls" and then need to clear the original workbook called
"inventry.xls".

macro called "newday" used to work to clear the page after promting the user
if they are sure they need to clear. now it stops runing after it
create/update "prevdayinventry.xls"

here is a extract from my macro:


Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "'ok' to clear page ... 'cancel' to go back" ' Define message.
Style = vbOKCancel ' Define buttons.
Title = "Maykent t/a KFC" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title)
If Response = vbOKCancel Then ' User chose Yes.


Sheets("DATA").Select

ChDir "C:\maykent\inventry"

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs
Filename:="C:\maykent\inventry\prevdayINVENTRY.xls ", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


Application.DisplayAlerts = True


Workbooks.Open Filename:= _
"C:\maykent\inventry\inventry.xls"

Windows("prevdayINVENTRY.xls").Activate
ActiveWindow.Close savechanges:=True



Windows("INVENTRY.xls").Activate

Sheets("data").Select

Range("b93").Select
Selection.ClearContents


Sheets("data capture").Select
Range("C1:C48").Select
Selection.ClearContents