Combining multiple workbooks with single sheets into one workbook with multiple sheet

  • I am trying to use this code. It has worked very well in the past but has stopped working for some reason.


    Here is mine code:-




    I've modified it to combine CSV files.


    Now, it breaks and says 'type mismatch' on this line:

    Code
    If LastCell.Value = "" And LastCell.Address = Range("$A$1").Address Then


    Aside from that, I'd like to install this in my personal macro workbook - but have the "result" (the combined workbook) in a new, blank workbook.


    Any tips?


    THANKS!

  • Re: Combining multiple workbooks with single sheets into one workbook with multiple s


    The code works fine for me... no issues whatsoever.


    Perhaps if you posted an (anonymous, cut-down if necessary) copy of your original workbook with the code and a sample of the CSV files, it could be tested with the actual data you/re using?

  • Re: Combining multiple workbooks with single sheets into one workbook with multiple s


    Thanks, this prompted me to look at my data. Two workbooks only had data on row1. Adding data to row2 resolved the error. I've no idea how to add error checking for that. I don't these particular workbooks merged (row 1 is a header row, so the workbook is effectively empty) but it would be nice for the code not to fail is there is no row2.


    Otherwise, I'm still looking for a way to store the code in personal but perform/store the merge in a new workbook.


    THANKS!!

  • Re: Combining multiple workbooks with single sheets into one workbook with multiple s


    Just add a check...

    Code
    If LastCell.Row > 1 then
          '// process it
          ...
       End If


    Please don't requote posts. it clutters things and the post is available to read by scrolling up a little...

  • Re: Combining multiple workbooks with single sheets into one workbook with multiple s


    Sorry about quoting posts. In another programming forum it's encouraged and common practice due to people deleting or editing posts. I shan't do it here!


    I've tried to implement your check as you posted it, and got a type mismatch. When I tried to peg it on to the end of an existing IF, I get a type mismatch.


    Any ideas?



    Thanks!

  • Re: Combining multiple workbooks with single sheets into one workbook with multiple s


    Changed slightly... Try this:


    I'm not saying that's the most elegant way of doing that - it's just the quickest and easiest - but you need to verify the check is valid.

  • Re: Combining multiple workbooks with single sheets into one workbook with multiple s


    Thanks - but the code generates a type mismatch on

    Code
    If LastCell.Value = vbNullString And LastCell.Row <= 2 Then


    No problems if the more or less empty workbooks aren't in the target folder.

  • Re: Combining multiple workbooks with single sheets into one workbook with multiple s


    Sorry - didn't see your attachment as it scrolled off the first page - and I rarely move off page 1.


    The issue is because you have an error in the last cell in one file. I realise this is a one row file in any case but you cannot evaluate a function with an error.


    Not sure exactly what you want to do. Report the fact there's an error? But then it'll only report those files that have an error in the last cell which in not very likely unless that's a result of other processing and any such files will only have one line of data... or just import regardless.


    Code below has 2 options:


    1. Import errors. You can then use the consolidated file to find errors
    2. Don't import, but report that files were skipped due to data error/no data/



    Comment/Uncomment lines as needed.

  • Re: Combining multiple workbooks with single sheets into one workbook with multiple s


    That error (and I can't check this as am using 2003 at the moment) seems to occur when you use the workbook on machines with XL2007 (or later) & XL2003 and is related to the max allowed number of rows and columns in the different versions. Sorry but the code works fine for me on 2003 so am unable to comment further.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!