worksheet saveas workbook

G

Guest

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
 
N

Norman Jones

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
'=========================>>
 
N

Norman Jones

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
'=========================>>
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top