Case VBA

[rakesh seebaruth]

Hi Guys

I have the following vba codes

Sub d()
Range("b2") = Format(Date, "dd/mmmm/yyyy")
Const mydrive = "C:"
Const mydir = "Users\rakesh\Desktop\test"
Dim myname As String
Dim ss As String
myname = Sheets("sheet1").Range("e5").Text & Format(Date, "dd-mmm-yy") & ".xls"
ss = mydrive & "" & mydir & "" & myname & ".xls"
Application.DisplayAlerts = False
ActiveWorkbook.SaveCopyAs Filename:=ss
Application.DisplayAlerts = True


End Sub

the following vba works perfectly i want to amend the above as follows :

I want to insert a Case statement in the above like that

Select Case Range("D2").Value
Case "CB"

If D2="CB" then save file in folder C:Users\rakesh\Desktop\test\corporate.Folder corporate already exists in folder C:Users\rakesh\Desktop\test.
If D2="PB" then save file in folder C:Users\rakesh\Desktop\test\private banking.Folder private banking already exists in folder C:Users\rakesh\Desktop\test.

Thanks in advance

Regards

Rakesh

 

[James006]

Hello Rakesh,

Below is your modified macro ... to be tested ...

Sub d2()
Const mydrive = "C:"
Dim mydir As String
Dim myname As String
Dim ss As String


  mydir = "Users\rakesh\Desktop\test"
  Select Case Range("D2").Value
    Case "CB"
    mydir = mydir & "\corporate"
    Case "PB"
    mydir = mydir & "\private banking"
  End Select


Range("b2") = Format(Date, "dd/mmmm/yyyy")
myname = Sheets("sheet1").Range("e5").Text & Format(Date, "dd-mmm-yy") & ".xls"
ss = mydrive & "" & mydir & "" & myname


Application.DisplayAlerts = False
ActiveWorkbook.SaveCopyAs Filename:=ss
Application.DisplayAlerts = True


End Sub

Hope this will help

P.S. LOVE your Country ... !!!

 

[rakesh seebaruth]

Hello James

I am getting the following error

Run Time Error 1004 Microsoft Excel cannot access the file C:\Users\rakesh\Desktop\test\corporateRAKESH16-Mar-19.xls

regards

rakesh

 

[rakesh seebaruth]

Hello again James

i have tried to modify the above as follows :-

Sub test()
On Error Resume Next

Range("B2") = Format(Date, "dd/mmmm/yyyy")
Select Case Range("D2").Value
Case "CB"
Sheets("sheet1").Select
ChDir "C:\Users\rakesh\Desktop\test1\corporate"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="C:\Users\rakesh\Desktop\test1" & Range("e5").Text & Format(Date, "dd-mmm-yy"), _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
Case "PB"
Sheets("sheet1").Select
ChDir "C:\Users\rakesh\Desktop\test1\private"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="C:\Users\rakesh\Desktop\test1\private" & Range("e5").Text & Format(Date, "dd-mmm-yy"), _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End Select

I have two folders in folder test 1 namely corporate and private.

When i run the vba , if D2=CB then the file is saved in the folder test1 not in the folder corporate

Thanks

rakesh

 

[James006]

Hello,

Corrected macro ...

Sub d2()
Const mydrive = "C:"
Dim mydir As String
Dim myname As String
Dim ss As String


  mydir = "Users\rakesh\Desktop\test"
  Select Case Range("D2").Value
    Case "CB"
    mydir = mydir & "\corporate\"
    Case "PB"
    mydir = mydir & "\private banking\"
  End Select


Range("b2") = Format(Date, "dd/mmmm/yyyy")
myname = Sheets("sheet1").Range("e5").Text & Format(Date, "dd-mmm-yy") & ".xls"
ss = mydrive & "" & mydir & "" & myname


Application.DisplayAlerts = False
ActiveWorkbook.SaveCopyAs Filename:=ss
Application.DisplayAlerts = True


End Sub

Left out a \ in the string mydir ...

Hope this version will work properly