Hi,

i have tried to do this and asked the question in General questions but to
no avail. i am looking for some help on a routine to carry out the following:

save the active sheet as a separate workbook in a specific location

the specific location is in the same folder "S:\invoices" and the folder
contains sub folders Jan to Dec.

i need the worksheet to save in this location by date and sheet/ client
name. for example:
client name = range("B23")
date = ("H14")

so my worksheet i need to save is 2945, the client name is "my client" and
the date is 23rd July 2005.
my sheet would be saved as

S:\invoices\July\2945 my client
( sheet only in a book not original book saved here)

cqan anyone help me with this as i need to close this project and i cannot
seem to get it at all.

thanks in anticipation,


Nigel

Hi Nigel,

The origin of 2945 is not clear to me, but subject to that reservation, try:

'=========================
Public Sub Try()
Dim strClient As String
Dim strDate As String
Dim strPath As String
Dim sStr As String
Dim arr As Variant

arr = Array("January", "February", "March", _
"April", "May", "June", "July", _
"August", "September", "October", _
"November", "December")

strPath = "C:" '"S:\invoices"
strClient = ActiveSheet.Range("B23").Value

strDate = Month(ActiveSheet.Range("H14"))
strDate = arr(strDate - 1)

sStr = strPath & "\" & strDate & "\" & 2945 & strClient

MsgBox sStr

ActiveSheet.Copy

ActiveWorkbook.SaveAs sStr

End Sub
'=========================

---
Regards,
Norman



"Nigel" wrote in message
...
Hi,

i have tried to do this and asked the question in General questions but to
no avail. i am looking for some help on a routine to carry out the
following:

save the active sheet as a separate workbook in a specific location

the specific location is in the same folder "S:\invoices" and the folder
contains sub folders Jan to Dec.

i need the worksheet to save in this location by date and sheet/ client
name. for example:
client name = range("B23")
date = ("H14")

so my worksheet i need to save is 2945, the client name is "my client" and
the date is 23rd July 2005.
my sheet would be saved as

S:\invoices\July\2945 my client
( sheet only in a book not original book saved here)

cqan anyone help me with this as i need to close this project and i cannot
seem to get it at all.

thanks in anticipation,


Nigel

Hi Nigel,

Correcting the base directory and simplifying, try:

'=========================
Public Sub Try()

Dim strClient As String
Dim strDate As String
Dim strPath As String
Dim sStr As String

strPath = "S:\invoices"
strClient = ActiveSheet.Range("B23").Value
strDate = Month(ActiveSheet.Range("H14"))
strDate = Format(strDate, "mmmm")
sStr = strPath & "\" & strDate & "\" & 2945 & strClient

ActiveSheet.Copy

ActiveWorkbook.SaveAs sStr

End Sub
'=========================


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Nigel,

The origin of 2945 is not clear to me, but subject to that reservation,
try:

'=========================
Public Sub Try()
Dim strClient As String
Dim strDate As String
Dim strPath As String
Dim sStr As String
Dim arr As Variant

arr = Array("January", "February", "March", _
"April", "May", "June", "July", _
"August", "September", "October", _
"November", "December")

strPath = "C:" '"S:\invoices"
strClient = ActiveSheet.Range("B23").Value

strDate = Month(ActiveSheet.Range("H14"))
strDate = arr(strDate - 1)

sStr = strPath & "\" & strDate & "\" & 2945 & strClient

MsgBox sStr

ActiveSheet.Copy

ActiveWorkbook.SaveAs sStr

End Sub
'=========================

---
Regards,
Norman



"Nigel" wrote in message
...
Hi,

i have tried to do this and asked the question in General questions but
to
no avail. i am looking for some help on a routine to carry out the
following:

save the active sheet as a separate workbook in a specific location

the specific location is in the same folder "S:\invoices" and the folder
contains sub folders Jan to Dec.

i need the worksheet to save in this location by date and sheet/ client
name. for example:
client name = range("B23")
date = ("H14")

so my worksheet i need to save is 2945, the client name is "my client"
and
the date is 23rd July 2005.
my sheet would be saved as

S:\invoices\July\2945 my client
( sheet only in a book not original book saved here)

cqan anyone help me with this as i need to close this project and i
cannot
seem to get it at all.

thanks in anticipation,


Nigel