Use VLOOKUP function. Try it using a worksheet formula, then incorporate it
into your code use Application.Workbook Function

With ActiveSheet
mycode = Application.WorksheetFunction.VLookup(.Range("B4") , Sheets("Trans
Types & Sources").Range("C1:I5"), 7, False)
End With

The above assumes your lookup table is in the range C1:I5, change as
required.

myCode can then be substituted for the xxxxx's in your file name.

fname = "XXAR_INVOICES_102_" & myCode & "WORKCOMP_" _
& Format(Range("G4").Value, "yyyy_mm_dd_" & Format(Now,
"hh-mm-ss"))


--

Regards,
Nigel




"ViViC via OfficeKB.com" <u39049@uwe wrote in message
news:7c8f5439f6491@uwe...
Hi All,

I have created a macro that saves a new workbook with a specifically
defined
name. Now I have been asked to make it available for 10 individual clients
and I dont want too have 10 different client workbooks to update when
required. The process is the same for all clients.

Client clicks command button to create a CSV file and then displays the
FileSaveAs dialog with the defined file name.

The code is as follows

Sub SaveCSV()
Dim fname As Variant
Dim Filesavename As Variant
Dim ws As Worksheet
Set ws = Worksheets("Trans Types & Sources")

fname = "XXAR_INVOICES_102_DCA_WORKCOMP_" & Format(Range
("G4").Value,
"yyyy_mm_dd_" & Format(Now, "hh-mm-ss"))
Filesavename = Application.GetSaveAsFilename(InitialFileName:=fna me, _
FileFilter:="CSV Files, *.csv")

If Filesavename = False Then
ActiveWorkbook.Close savechanges:=False
Worksheets("Main").Select
Application.ScreenUpdating = True
Range("D17").Select
MsgBox "Process Cancelled at your request and no invoice data has
been lost", vbYes, "Warning"
Else
'Saves created csv file to directory for emailing
ActiveWorkbook.SaveAs Filename:=Filesavename, FileFormat:=xlCSV

Call EmailCSV
Call CanFile
Call DelRecords
Range("D17").Select
MsgBox "CSV file created saved and emailed", vbYes,
"Completed"

End If
End Sub

This works now but I need to change it to accommodate all clients with a
specific file name for each client.

This is what I am trying to do but in words.

The active sheet is XXAR_INVOICES_102_
Use the value in active sheet cell B4 to
Lookup the value in sheet Trans Types & Sources in column C and then
Take value from Column I on the same row (3 digit Alpha Code) then
Add to file name to be saved

The xxxxxs show where the value is required to be added to file name

fname = "XXAR_INVOICES_102_" & XXXXX & "WORKCOMP_" _
& Format(Range("G4").Value, "yyyy_mm_dd_" & Format(Now,
"hh-mm-ss")
)

I hope you can understand what is needed. Many thanks for the help

ViViC

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200712/1

Thanks Nigel,

The code kept bombing on mycode = Application.WorksheetFunction.VLookup(.
Range("B4"), Sheets("Trans Types & Sources").Range("C1:I5"), 7, False), Run
Time Error '9' Subscript out of range.

I changed Sheets("Trans Types & Sources") to ThisWorkbook.Worksheets("Trans
Types & Sources") and this worked brillantly.

Your help is greatly appreciated and your worth your weight in gold.

Many thanks again

ViViC

Nigel wrote:
Use VLOOKUP function. Try it using a worksheet formula, then incorporate it
into your code use Application.Workbook Function

With ActiveSheet
mycode = Application.WorksheetFunction.VLookup(.Range("B4") , Sheets("Trans
Types & Sources").Range("C1:I5"), 7, False)
End With

The above assumes your lookup table is in the range C1:I5, change as
required.

myCode can then be substituted for the xxxxx's in your file name.

fname = "XXAR_INVOICES_102_" & myCode & "WORKCOMP_" _
& Format(Range("G4").Value, "yyyy_mm_dd_" & Format(Now,
"hh-mm-ss"))

Hi All,

[quoted text clipped - 63 lines]

ViViC

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200712/1