Refining Macro to Consolidate Columns into one Sheet

Hi All,

Been reading bits and pieces around here (and various other sites) as I get myself back up to speed in VBA scripting (it's been several months since I've had to do anything and I'm pretty rusty already).

Was hoping someone would be kind enough to assist me in refining down my code to be shorter and more to the point?

Specifically, I would really like help in replacing my cheatsheet (Sheet19 in the code) I used to replace the Column Number with a subsequent Column Letter (Using Function: "ColLetterFromNo"... I think R1C1 might be an option for me??) and figuring out a way to further variable-ize the code so that it will eventually work nearly automatically. Also, I've been having the most difficult time getting the clear portion of the macro to work on the Import sheet, so I resorted to having the macro use the CountA WorksheetFunction again (there's a better way, I am certain of this, but I can't seem to get it just right...) to make it work.

The point of this sheet is to check the Headers on my "Import" Sheet, loop through the subsequent sheets and pair up those Headers with that of the current sheet it loops into, determine the final row in the sheet (Note: Final Row of a specific column, I maintain an arrayed ID # throughout the entire sheet, which prohibits normal last row lookups) and then copy those columns to the Import Sheet. As it continues through, it appends the data rather than replaces as well. Eventually I will be making this entire sheet copy its data to another workbook to perform a SQL database update.

I've spent about 2 days getting to this point.

Please Login or Register  to view this content.

Notes: I have had to comment out anything in the 'E' category as I have not set up my data sheet to handle everything in that column appropriately yet. I cannot share the workbook (maybe I can rig up a small test if needed) but I will be handling up to a couple thousand rows per sheet potentially and the column layout of each sheet is vastly different, fortunately I am only pulling the similar data from each sheet to my "Import" worksheet which makes it a little simpler.


Much appreciation to whomever is willing to lend me a hand here.




LW

Thanks for that rylo.

Below is my updated code using your suggestions and changing some of the styling to use the Cells() syntax. I finally got it to work with the Column Numbers by changing some of the variables to be 'Long' instead of Strings after doing a bit more of reading and testing. Then I also modified the Import sheet clearing code to be more effective, I think this is a much better script than the one from before.

Is there perhaps a way I can modify the script to handle a variable number of headers from the Import Tab (I used A-Fimps to define the individual headers, then A-Floops to search for them in each sheet) without assigning each header to a variable and looping through all of them? In the future I may need to import more information and I'd rather not copy the code continuously (or leave directions to do so) and simply have it parse out each cell in the A1 row/header and search for each one in the subsequent sheets.

Also, can excel loop through the Sheet codenames rather than Sheet location names? Example, I name all of my extra sheets Help1-4 for instance, my data will be Sheet1-XX, can it loop through Sheet1, Sheet2, Sheet9... using their assigned codenames?

Please Login or Register  to view this content.

Hi agin.

Took a bit of a punt on some of your structures, and how I interpret your code and think this may get close.

Please Login or Register  to view this content.

rylo

Wow, impressive macro. My only problem now is that it addresses what range to copy based on if the cells contain a value or not for each column. I need a static number to be copied on each sheet and a variable number to be copied from sheet to sheet, if you understand what I'm saying. When I run it for all of my sheets, from 1 to 998 (the length of my table) for the ID #'s, descriptions and suppliers because I maintain a dash in each column. So those fields should be omitted from being copied despite containing a value of some sort.

We could make use of the Item # field to create the static column length (as it will never contain a value if actually empty).

Thanks for the help to this point, I can't believe what I wrote was able to be simplified so far down yet still work on a variable number of fields. It's a much quicker script now too.

E: Here's what I've been trying/tested. It's been working all day for me, any recommendations on the changes I made? I tested adding new columns and those appear to have appended data perfectly as well.

Please Login or Register  to view this content.