Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup function in code
Hi,
The code below is to create a workbook for each of the sheets in the array and then save each workbook and save into its specific directory which is stored in a range. I attempted to use the Lookup function but it is giving me a type mismatch error. Please advise. Cheers- Dean Range named "dir" Group Directory ops "C:\Documents and Settings\dbrunne\My Documents\Overheads\Test \ops.xls" sales "C:\Documents and Settings\dbrunne\My Documents\Overheads\Test \sales.xls" mark "C:\Documents and Settings\dbrunne\My Documents\Overheads\Test \mark.xls" lnl "C:\Documents and Settings\dbrunne\My Documents\Overheads\Test \lnl.xls" fwp "C:\Documents and Settings\dbrunne\My Documents\Overheads\Test \fwp.xls" pbc "C:\Documents and Settings\dbrunne\My Documents\Overheads\Test \pbc.xls" rtd "C:\Documents and Settings\dbrunne\My Documents\Overheads\Test \rtd.xls" wine "C:\Documents and Settings\dbrunne\My Documents\Overheads\Test \wine.xls" CODE Sub CreateWorkbooks() Dim WbMain As Workbook Dim Wb As Workbook Dim sh As Worksheet Dim FilePath As String Dim MyArray As Variant Dim rng As Range Application.ScreenUpdating = False Application.EnableEvents = False Set WbMain = ThisWorkbook MyArray = Array("ops", "sales", "mark", "lnl", "fwp", "pbc", "rtd", "wine") Set rng = Range("dir") For Each sh In WbMain.Worksheets(MyArray) If sh.Visible = -1 Then sh.Copy Set Wb = ActiveWorkbook FilePath = Application.WorksheetFunction.Lookup(MyArray, rng) Wb.SaveAs FilePath Wb.Close False End If Next sh Application.ScreenUpdating = True Application.EnableEvents = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup function in code
Dean,
Give this a try. Sub CreateWorkbooks() Dim WbMain As Workbook Dim Wb As Workbook Dim sh As Worksheet Dim FilePath As String Dim MyArray As Variant Dim rng As Range Dim i As Integer Application.ScreenUpdating = False Application.EnableEvents = False Set WbMain = ThisWorkbook MyArray = Array("ops", "sales", "mark", "lnl", "fwp", "pbc", "rtd", "wine") Set rng = Range("dir") For i = 0 To UBound(MyArray) Set sh = WbMain.Worksheets(MyArray(i)) If sh.Visible = -1 Then sh.Copy Set Wb = ActiveWorkbook FilePath = Application.WorksheetFunction.VLookup(MyArray(i), rng, 2, False) Wb.SaveAs FilePath Wb.Close False End If Next i Application.ScreenUpdating = True Application.EnableEvents = True End Sub -- Hope that helps. Vergel Adriano " wrote: Hi, The code below is to create a workbook for each of the sheets in the array and then save each workbook and save into its specific directory which is stored in a range. I attempted to use the Lookup function but it is giving me a type mismatch error. Please advise. Cheers- Dean Range named "dir" Group Directory ops "C:\Documents and Settings\dbrunne\My Documents\Overheads\Test \ops.xls" sales "C:\Documents and Settings\dbrunne\My Documents\Overheads\Test \sales.xls" mark "C:\Documents and Settings\dbrunne\My Documents\Overheads\Test \mark.xls" lnl "C:\Documents and Settings\dbrunne\My Documents\Overheads\Test \lnl.xls" fwp "C:\Documents and Settings\dbrunne\My Documents\Overheads\Test \fwp.xls" pbc "C:\Documents and Settings\dbrunne\My Documents\Overheads\Test \pbc.xls" rtd "C:\Documents and Settings\dbrunne\My Documents\Overheads\Test \rtd.xls" wine "C:\Documents and Settings\dbrunne\My Documents\Overheads\Test \wine.xls" CODE Sub CreateWorkbooks() Dim WbMain As Workbook Dim Wb As Workbook Dim sh As Worksheet Dim FilePath As String Dim MyArray As Variant Dim rng As Range Application.ScreenUpdating = False Application.EnableEvents = False Set WbMain = ThisWorkbook MyArray = Array("ops", "sales", "mark", "lnl", "fwp", "pbc", "rtd", "wine") Set rng = Range("dir") For Each sh In WbMain.Worksheets(MyArray) If sh.Visible = -1 Then sh.Copy Set Wb = ActiveWorkbook FilePath = Application.WorksheetFunction.Lookup(MyArray, rng) Wb.SaveAs FilePath Wb.Close False End If Next sh Application.ScreenUpdating = True Application.EnableEvents = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU | Excel Discussion (Misc queries) | |||
Excel Data Validation/Lookup function does function correcty | Excel Worksheet Functions | |||
how to combine an IF Function with a lookup function to determine | Excel Worksheet Functions | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) | |||
vba code for lookup worksheet function | Excel Programming |