Merging two sheets/files into one?

i'd use a vlookup in File 1.

insert an extra column in File 1. use a formula like this:

=vlookup($b2,'[File 2.xls]Client Details'!$a$2:$b$1000,2,0)
{change ranges to suit}
copy down as needed. once all values are obtained, select all (Ctrl + A), copy/paste special (values). then delete File 2.

hth

THANKS!!!!!!!!!!!!!!!!!!!!!!!!!

That worked perfectly!!!!! You learn something everyday. I never knew what vlookup did until now and will make my life a million times easier!!

Thanks!

your welcome!

{that was my first question here too! }

firefytr said:

i'd use a vlookup in File 1.

insert an extra column in File 1. use a formula like this:

=vlookup($b2,'[File 2.xls]Client Details'!$a$2:$b$1000,2,0)
{change ranges to suit}
copy down as needed. once all values are obtained, select all (Ctrl + A), copy/paste special (values). then delete File 2.

hth

Click to expand...

I have the same problem. but when i try copy down what you wrote and paste it into my excel sheet 1 it does not work, it gives me a #value error. can you please help me

The #VALUE error means it is not finding an exact match, as specified by the last "0" in the formula. Either change to a 1 or make sure you have exact matches. This does include leading/trailing spaces. If you need more help, post your stuff on the board w/ the HTML Maker.

I have a similar problem...

I have a total of 3 worksheets under the same file. Now each worksheet has the same columns and the first row is identical. It is set like this:
Project; Date; Day; Time Begin; Time End; Project Description; etc.
1; 12/27/04; Monday; 7:00 AM; 8:00 AM; etc..
2; 12/27/04; Monday; 7:00 AM; 8:00 AM; etc..
3; 12/28/04; Tuesday; 7:00 AM; 8:00 AM; etc..
4; 12/297/04; Wed; 7:00 AM; 8:00 AM; etc..

What I want to do is merge or consolidate all three worksheets into one worksheet that will give me a list of all of the projects. In essence take the work from worksheet A + worksheet B + worksheet C. I don't know if its possible, if it is can you please please help me. I think I have tried everything and I can't get this...and I have tried a pivot table, but it comes out really weird and its not easy to interpret the info. Any assistance you can provide would be greatly appreciated. Thanks

Hello, and welcome to the board!


Unsure as to your specifics, but maybe this will help ...



<font face=Tahoma New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> ConsolidateThreeSheets()
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> lastCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> newWs <SPAN style="color:#00007F">As</SPAN> Worksheet, ws <SPAN style="color:#00007F">As</SPAN> Worksheet, origWs <SPAN style="color:#00007F">As</SPAN> Worksheet
    <SPAN style="color:#00007F">Set</SPAN> origWs = ActiveSheet
    <SPAN style="color:#00007F">Set</SPAN> newWs = Worksheets.Add
    Sheets("Sheet1").Range("A1", Sheets("Sheet1").Range("IV1").End(xlToLeft)).Copy
    newWs.Range("A1").PasteSpecial (xlPasteValues)
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets
        lastRow = 0: lastCol = 0
        <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> ws.Name
        <SPAN style="color:#00007F">Case</SPAN> "Sheet1", "Sheet2", "Sheet3"
            lastRow = ws.Range("A65536").End(xlUp).Row
            lastCol = ws.Range("IV1").End(xlToLeft).Column
            ws.Range("A2", ws.Cells(lastRow, lastCol)).Copy
            newWs.Range("A65536").End(xlUp).Offset(1).PasteSpecial (xlPasteValues)
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> ws
    newWs.Move After:=Sheets(ActiveWorkbook.Sheets.Count)
    origWs.Activate
    Application.CutCopyMode = <SPAN style="color:#00007F">False</SPAN>
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
    MsgBox "Complete!"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>


{Okay, this thread has now been hijacked. }

Thank you thank you thank you....it worked Firefytr...you are a life saver. Thank you again and happy holidays

Hey Firefytr, your code looks good, but i wonder if i could use it in my case? and also how can i post a HTML drawing on here? can you give me a quick info on how to? thanks