rakesh seebaruth

Active Member
Joined
Oct 6, 2011
Messages
303
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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top