Extract Data From Multiple Sheets Into One

  • Hi there,

    This was originally going to be a simple Select Case question, but upon introducing my problem I realise that there is likely a much better way of attacking my "big picture".

    I know I will likely get told off for the code given that it is basically a series of loops and will be time consuming because of this. However, this is a one-time code that I need to run on this spreadsheet so I can get the data into a "correct" format for future use.

    Currently, I have hundreds of sheets that are simialr to the attached TA0632TEST and TA0632TEST2 sheets, each with a differing amount of columns based on the number of work days for that particular month. The code only needs to be performed on sheets starting with the initials of an employee (shown in select case in code).

    I've been thinking of better ways to have data entered/managed and had thought it best to have all raw data in a single sheet which can then be filtered/looked up/pivot tabled/custom viewed etc. and all the other fun stuff like how it should be done :-).

    I am open to other ideas about how best to get this historic data into a managable format.

    The problem I am running into at the moment is that where the Select Case is checking if the sheet name starts with the initials and project number, it doesn't seem to recognise if it is correct (and therefore perform the actions).

    I have stepped through the code and when I use the immediate window to manually check:

    ? ws.Name Like "TA0632*"

    I get True as an answer, but the code goes on to the next case as though it is false.

    Here is the code I have so far, which I'm sure can be trimmed down loads:

    I appreciate any thoughts as to the best method, or advise as to why my select case is not working as expected.

    Kind regards,
    Averil Pretty

  • Re: Extract Data From Multiple Sheets Into One


    I have read through the above posts as well as others. In fact, I've been searching the site since 5am this morning, and no case seems to be like mine unless I am searching incorrectly, but I'll keep trawling through.

    In the mean time, does anyone have suggestions as to why the Select Case doesn't seem to be working as expected?


    [FONT="Comic Sans MS"]Averil Pretty[/FONT]

Participate now!

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