Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    code help (2003)

    Hi all,

    First let me say that I am very happy you are back and the forum is united back with its members.

    Next I really need a code help, what I have a folder named template which has multi sub folders, and in each sub folder there are multi workbooks, each has multi sheets. I want a code that will read thru the sheets and look for a text string that starts with the word "Country" which has two locations column a & d in the same row and copy the rows under that rows until it found another text string in column a that starts with the word "Total", then stop. And output the results with the folder and the workbook names in a separate folder.

    Regards,
    dubdub
    TIA
    dubdub

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: code help (2003)

    We need some more details. Here are some that come immediately to mind:
    Is the code supposed to look through all the files and stop after finding a sheet matching this or do it for all the sheets it can find?
    If for all, where do you want the subsequent rows stored? In the same workbook or a new one?

    Where do you want the folder and filename stored in the sheet? Is the sheetname supposed to be stored somewhere also (if so where)?

    Where do you want to store the file(s) and what do you want to call it/them?

    Will there ever be occurences of a "country" in "A" but not found in "D"? Will there be multiple occurences of "Country"? What if there is a country in A&D between the start row and the "Total row"? Do you keep it in this group or split them?

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: code help (2003)

    Thanks Steve for your reply and my answers to your questions in the same order are:

    Do it for all the sheets it can find or enable me to specify some common sheet names in these workbooks.
    In a new one.
    The folder, the workbook and the sheet names will be in columns a:c followed by the extracted data.
    The extracted data will be stored in a new folder "master" in a workbook called "extract" in a sheet called "summary".
    The sheets I have checked has the text string with the word "country" in a others has in d, there is a possibilities of multiple occurrences of country in the same sheet but in different rows, and if occurs it will be good to split and from what I saw there are no country between the first country appearance and the total row.

    One more thing for those sheets where country starts at col. D I want the extract to start from a and all the headings in country row to be the heading for the extracted data in the new sheet

    Regards,
    dubdub
    TIA
    dubdub

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: code help (2003)

    I am still confused.

    You indicate the each "Country ..Total" set of rows should be in a separate workbook. If a sheet has multiple ones that could mean that there are more of these "extract" workbooks than the number you started with. Each would need a different name.

    [Did you mean to say that you want them all in the same workbook? How should they be put in?
    Row 1(A-C): Folder name, workbook name, sheetname
    Row2-x (depending on ranges of Country to Total)
    Row (x+1)(A-C): Folder name, workbook name, sheetname
    Rowx+2-x+y (depending on ranges of Country to Total)
    etc]

    I am also not clear on what to do with COuntry starting in A but not in D or starting in D but not in A.

    Also what to do if there is a country in A and D but no "total" after this row.

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: code help (2003)

    Steve,

    Thanks again for your reply.

    The data I want to copy and paste in a separate folder and workbook is located in different sheets and most of the times they do not share a common name but they share two format. Sheet1 in the attached book reflect one and sheet2 reflect the other one. As you can notice each format consist of four sections and each covers a certain rage, these sections are the general information, future period data, previous period data and the delta or the difference between the two.
    What I want is to extract the bottom three sections from all sheets in all work books and have them arranged right to each other. That is to say future period then previous period and then the delta, and the two format to be aligned as in sheet 3 under one headings.

    dubdub
    Attached Files Attached Files
    TIA
    dubdub

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: code help (2003)

    Now I am really confused. Your example does not fit at all what you were describing earlier. In neither sheet1 nor sheet2 do you have Country starting a cell in either Col A or COlumn D let alone in both. In neither type is there a country in 2 columns in the same row.

    Also, based on what you asked for, only the rows from the "Country" until the "total count" would be copied. The rows with "Total New Resl Count", "Total No-11 RE Count", "MAC", and "STAT" would not be copied since they are after the first "Total".

    Your example only copied the "Future Period" item, do you only want the items with "Future period". If judged by "Country to "total" the period row is not only not copied but not considered in determining what is copied since they occur before the "Country". Do you really want to copy the items from any of the rows with "Future Period" to the row with "STAT"?

    Is it always "Country Area District" or are there columns starting with "country" and having other text? Should the "country" column always be column F in the extract or are there some having it as a much later column?

    You also originally asked to copy all the rows between "Country" and "total" but if you are going to shift the columns in some of them this will not be possible since a row always has 256 columns. Is there a set number of columns that you want copied? Should the number of columns be determined at runtime (if so, how can one tell the number the columns: is the row with "Country" filled in completely?)?

    Also in your example the 2 different types are on separate sheets, but in you combined version in C1 they are listed as the same sheet. Which sheet should be listed in C1 in this example, sheet1 or sheet2, when they are from different sheets in the same book.

    Also If they are in different workbooks or different folders how would that be listed in sheet3 or should it be put into a new workbook than the extracts from other workbooks and folders?


    Steve

  7. #7
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: code help (2003)

    Steve,

    Sorry for the confusion, yes it starts at columns c & e and there is no country in 2 columns in the same row.
    Yes only form the country row until the total count will be copied.
    No only from country row to the total count row.
    No there are no columns staring with country area district in the same row, the only problem is they need to be aligned in the extract such that country area district column (F) from both format will be in the same column, and columns d&e will contain the heading and the data from sheet2.
    The columns will be shifted and aligned to have country area district data from both format to be in the same column (F) and the row with country is filled completely and by completely i mean if you consider sheet2 format then the row will be filled 30-40 columns from column d.
    Sheet2 as this will ease in the alignment process.

    In summary extract the range from country area district to total count from all sheets in all workbooks in all sub folders for both formats in sheet 1 &2 and align them such that sheet1 data follow sheet2 to ease the alignment of the country area district to be in column F.

    dubdub
    TIA
    dubdub

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: code help (2003)

    How many columns get copied?
    Do you only copy "Future periods"
    Are teh "Country..." all "Country Area District" or will there be some starting with "Country" and ending with something else?
    Will the first "Starting with 'Total'" be "Total Count" or could it be something else?

    Are there sheets with no extracting?

    If we assume you have 3 workbooks:
    WorkbookA is in Folder A and has 2 sheets exactly like your sheet1 and sheet2 example
    WorkbookB is in Folder A and has 1 sheet exactly like your sheet1 example
    WorkbookC is in Folder B and has 1 sheet exactly like your sheet2 example

    How many workbooks/worksheets should be created and what would they look like and what would their names be?

    Steve

  9. #9
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: code help (2003)

    Steve,

    As I mentioned in my early reply and based in sheet2 format 35 columns.
    copy the future period,the previous and the delta for each format.
    the "Country Area District" is a distinct text string in all sheets.
    The starting total will be always “Total Count”.
    Yes.
    One workbook called extract and I was inspired by your example and have attached a file with a revised way of how the extracted data will look like hoping that it will make it easier to understand and to code.

    In short it will extract all sheet1 format sheets from all folders and workbooks into one sheet called”sheet1-format” and all sheet2 format sheets in another one called”sheet2-format” and both will be in the new workbook called extract. I hope I am not confusing the issue more.

    Regards, and thank you for your patient.
    dubdub
    Attached Files Attached Files
    TIA
    dubdub

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: code help (2003)

    Question has been reposted in <post:=666,723>post 666,723</post:>. This thread has been locked to prevent further duplication.

    Please post all replies in the other thread.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •