Need a simple VBA code to automate a repetitive task.

Hi,

Could you please give a solution for the following:

EXAMPLE

I receive a couple of excel files everyday via email. Every file i open will
have the name of a city

(eg. CA,NY,Boston,NJ) in the cell D9. I have Folders in the C Drive by viz
CA,NY,Boston,NJ. I want to write a VBA code will:

When the excel file is opened on running the macro it will look which city
name is mentioned in the cell D9 and copy the file to the relevant folder.
So if D9 contains NJ the file has to be save in the NJ folder. Right now
this activity is being done manually everyday.

Awaiting a reply.

Thankyou in advance.

Regards,

MADHU

Use this macro in Personal.xls or your own Add-In

Sub test()
Const StoreDir = "C:\T\"
Dim str As String

With ActiveWorkbook
str = .Worksheets(1).Range("D9").Value
.SaveAs StoreDir & str
End With
End Sub

Change C:\T\ to where you want to archive the files.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"madhu" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> Could you please give a solution for the following:
>
> EXAMPLE
>
> I receive a couple of excel files everyday via email. Every file i open
> will have the name of a city
>
> (eg. CA,NY,Boston,NJ) in the cell D9. I have Folders in the C Drive by
> viz CA,NY,Boston,NJ. I want to write a VBA code will:
>
> When the excel file is opened on running the macro it will look which city
> name is mentioned in the cell D9 and copy the file to the relevant folder.
> So if D9 contains NJ the file has to be save in the NJ folder. Right now
> this activity is being done manually everyday.
>
> Awaiting a reply.
>
> Thankyou in advance.
>
> Regards,
>
> MADHU
>
>

If Range("D9") contains the 3 values and you want to get just the last, look
up Split in help, this will split it into an array, and you can use the
UBound(array_name) to get the index of the last item.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Rob van Gelder" <[email protected]> wrote in message
news:[email protected]...
> Use this macro in Personal.xls or your own Add-In
>
> Sub test()
> Const StoreDir = "C:\T\"
> Dim str As String
>
> With ActiveWorkbook
> str = .Worksheets(1).Range("D9").Value
> .SaveAs StoreDir & str
> End With
> End Sub
>
> Change C:\T\ to where you want to archive the files.
>
> --
> Rob van Gelder - http://www.vangelder.co.nz/excel
>
>
> "madhu" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> >
> > Could you please give a solution for the following:
> >
> > EXAMPLE
> >
> > I receive a couple of excel files everyday via email. Every file i open
> > will have the name of a city
> >
> > (eg. CA,NY,Boston,NJ) in the cell D9. I have Folders in the C Drive by
> > viz CA,NY,Boston,NJ. I want to write a VBA code will:
> >
> > When the excel file is opened on running the macro it will look which
city
> > name is mentioned in the cell D9 and copy the file to the relevant
folder.
> > So if D9 contains NJ the file has to be save in the NJ folder. Right now
> > this activity is being done manually everyday.
> >
> > Awaiting a reply.
> >
> > Thankyou in advance.
> >
> > Regards,
> >
> > MADHU
> >
> >
>
>