auto saving activeworkbook with specific name

[Rohmadi02]

Hello Experts..

I need your help to point me to the right direction.
I have an Excel macro that extract data directly from SAP system , and when the data export it to Excel it's has workbook name as ALVXXL01(1).
Usually I use ActiveWorkbook.SaveCopyAs code to grab it and save it to desired folder and file name but sometimes it's won't work . it's saved the master file instead of the activeworkbook of ALVXXL01(1).

Now , I am trying to use below new code but it's still not works yet.

Sub SavecopyAsWorkbook()
Dim Wk As Workbook
Set Wk = ActiveWorkbook
Dim sWbk As String

Application.DisplayAlerts = False

If InStr(sWbk, "ALVX") <> 0 Then

Wk.SaveCopyAs Filename:="C:\Temp\testwbksave.xls"
End If

Application.ScreenUpdating = True

End Sub

Any help would be really appreciated! Thanks.

Regards,
Agung

[arlu1201]

In the above code, you have not assigned "sWbk" to anything. See if it works, if not let me know.

[Rohmadi02]

Hi Arlette, Thanks for the advise if I set "sWbk" as active workbook, this is not works. Pls complete the code as actually I am not expert on this , the code was obtained from forum.

Hi TMShucks,thanks for the info , I am newbi to this forum , tried to use code between them as below.

Sub SavecopyAsWorkbook()
Dim Wk As Workbook
Set Wk = ActiveWorkbook
Dim sWbk As String

Application.DisplayAlerts = False

If InStr(sWbk, "ALVX") <> 0 Then

Wk.SaveCopyAs Filename:="C:\Temp\testwbksave.xls"
End If

Application.ScreenUpdating = True

End Sub

[arlu1201]

You need to put code tags around your code. Only then, i will be able to assist you. Check the forum rules on how you can put the code tags.

[Rohmadi02]

Hello Arlette..
I have put the code tag around my code..
see below

Sub SavecopyAsWorkbook()
Dim Wk As Workbook
Set Wk = ActiveWorkbook
Dim sWbk As String

Application.DisplayAlerts = False

If InStr(sWbk, "ALVX") <> 0 Then

Wk.SaveCopyAs Filename:="C:\Temp\testwbksave.xls"
End If

Application.ScreenUpdating = True

End Sub

Pls assist on the correct script for Excel VBA.
Thanks.

[TMS]

@Rohmadi02: for future reference, you should edit the original post and add the code tags there. I will do it for you this time.

Regards

[Rohmadi02]

@TMShucks , Thanks for that, I appreciate it.

[Rohmadi02]

@Arlette, Can you pls assist me to correct my above code?

Thanks & regards,
Agung

[arlu1201]

Use this code -

Sub SavecopyAsWorkbook()
Dim Wk As Workbook
Set Wk = ActiveWorkbook

Application.DisplayAlerts = False

If InStr(Wk, "ALVX") <> 0 Then
    Wk.SaveAs Filename:="C:\Temp\testwbksave.xls"
End If

Application.ScreenUpdating = True

End Sub

[Rohmadi02]

Hi Arlette,
Thank you but it's got run time error '438': object does not support this property or method.
after press debug button, this line "If InStr(Wk, "ALVX") <> 0 Then" has yellow coloured.

any other alternatives suggestion??

Thank you .

[Rohmadi02]

Hi Arlette,
Thank you but it's got run time error '438': object does not support this property or method.
after press debug button, this line "If InStr(Wk, "ALVX") <> 0 Then" has yellow coloured.

any other alternatives suggestion??

Thank you .

[arlu1201]

Oops i am sorry, it had to be If InStr(Wk.name, "ALVX")

[Rohmadi02]

Hi Arlette,
Thank you so much ..It's works now..

Regards,
Agung