worksheet saveas workbook
 

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

worksheet saveas workbook
 

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

worksheet saveas workbook
 

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