rakesh seebaruth

Board Regular
Joined
Oct 6, 2011
Messages
237
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
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hello Rakesh,

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

Code:
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 ... !!! :)
 
Last edited:

rakesh seebaruth

Board Regular
Joined
Oct 6, 2011
Messages
237
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

Board Regular
Joined
Oct 6, 2011
Messages
237
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

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hello,

Corrected macro ...

Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,614
Messages
5,523,919
Members
409,542
Latest member
Shezz01

This Week's Hot Topics

Top