I have a project I am working on for myself. I have many parts of my macros complete. The attached spreadsheet only covers one part. The information below is also in the spreadsheet, but I am repeating it here, so folks can read it in the post without downloading the file.
- I get lists from a service with the information that I need
- There are two types of lists. Long Ones (LO) and Short Ones (SO). In my real-life data, SO has 13 headers and LO has 57
- I get 3 or more lists a week. I put all of the SO sheets into one Workbook and all of the LO sheets into another Workbook. - this part of a macro works fine -
- NOTE: All headers exist for each type of sheet, but they could be in random order
- My next step is to combine all of the worksheets into one master worksheet so I can upload them to my web service
- All the headers in the worksheets have the same names? I have found that the names might be COLUMN1 or misspelled instead of the intended column header and it might be in different column order. Any sheet could be slightly different. All headers should have the same name for the same data regardless of order
- I have a mix of sheets here because I don't know which active sheet will have which type of list when I open the files from a directory (not in this macro). What I have done so far. I created an array for the LO workbook and the SO workbook. Set the active worksheet in the workbook, then according to a count of header create an array for the sheet (SH) header. -All that works - THIS WORKBOOK
- "This is what I still need to do with the attached workbook.
a. I want to compare the SH headers to the LO or SO headersb. Display which SH header is not in the LO or SO header Arrayc. Ask if I want to rename the SH header to the LO or SO header that was different from SH.d. Rename the Header- Then I need to sort all of the columns and put them in the order that I want them. - I created that macro and that part works too.
I have been at this entire process for a week now and just can't figure out the comparison approach and renaming. If there is a better way, let me know. I am trying to keep from having to do too much manually.
Thanks in advance.
Kehf
Bookmarks