Quickest way to copy EXCEL Sheets

[blossomthe2nd]

Hi there

I have a workbook with about 20 tabs , each tab has about 30,000 lines of data.

This is my master workbook and I wish to write in another spreadsheet vlookups against it. if I was working with smaller amounts of data I would simply copy each tab over into one tab and use this to write my lookups against, but as theres so much data and I believe Excel has a row limitation I am unsure as to what is bets to do ?

Should I write a few ifs within my lookup or does anyone know a way aroud this ?

Thanks

Anne

 

[VoG]

You could try VlookAllSheets http://www.ozgrid.com/VBA/VlookupAllSheets.htm

 

[blossomthe2nd]

Thanks VOG,

Cant quite get it working, I'm getting a "Compile error : Invalid outside procedure" on

On Error Resume Next

Part of script , have you used this ? Can you advise

Thanks

 

[VoG]

It may not have pasted into the VBE correctly. Try the code below

Function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, Col_num As Integer, Optional Range_look As Boolean)
''''''''''''''''''''''''''''''''''''''''''''''''
'Written by OzGrid.com
'Use VLOOKUP to Look across ALL Worksheets and stops at the first match found.
'''''''''''''''''''''''''''''''''''''''''''''''''
Dim wSheet As Worksheet
Dim vFound
On Error Resume Next
    For Each wSheet In ActiveWorkbook.Worksheets
        With wSheet
        Set Tble_Array = .Range(Tble_Array.Address)
            vFound = WorksheetFunction.VLookup _
            (Look_Value, Tble_Array, _
            Col_num, Range_look)
        End With
        If Not IsEmpty(vFound) Then Exit For
    Next wSheet
    Set Tble_Array = Nothing
    VLOOKAllSheets = vFound
End Function

 

[blossomthe2nd]

Hi VOG,

Im sure Im making a meal out of this !!! I tried the script as pasted in but I'm getting error Ambigious name detected :VLOOKallSheets

The formula that created that error is
=VLOOKAllSheets(A4,B:G,1,FALSE)

I tried different variations of the font (!!) but still getting same error,

and if I try =vlookupallsheets(A6,B:G,1,FALSE) I'm still getting the NAME? Error

Sorry to harrass you, but can you please help ?

Thanks

 

[VoG]

You must have two versions of the function. Try deleting the original one that you inserted leaving just the code that I posted.

 

[blossomthe2nd]

Thanks VOG

Its all good now,

Appreciate it

Kind regards
Anne