Loop macro through all columns of worksheet

Hello all

I have this code which is intended to perform a required task: build a single, 2-column list from the filtered results of one (or several worksheets) from another workbook.

The macro that I have built performs the task as required, but my code makes specific reference to the 1st column of data within the source sheet, so the macro only performs the task for the 1st column.

How can I change the code so that it repeats the process for each subsequent column across the worksheet until it reaches the end (either a blank cell in row 1 OR the end of the sheet)? I have marked the 2 sections of the code that reference a specific column using comments containing ********** and ++++++++++ respectively.

The 2nd item on my wish-list (although I will be more than happy if I can only have the above) is to then repeat the process through a further 9 specified worksheets within the same workbook as each sheet is completed (I have marked this part of the code with //////////).

I have spent a whole lot of time investigating this and experimenting and I have previous posts which were unsuccessful. I am desperately hoping that there is someone out there who can come to my rescue as my next approach is going to have to be extremely manual.

Please Login or Register  to view this content.

Any thoughts???

Hi Berries

I have no way to test it but try this code and see if it does as you described

Please Login or Register  to view this content.

Place the names of your sheets in the array.

John

Hi Berries

It's late here tonight. I'll look at this tomorrow.

John

Although the macro could use a major rewrite to remove all the selecting and activating, addressing the commands directly to the ranges in questions, try just fixing this for now:

Please Login or Register  to view this content.

PASSWORD protected file...

Hi Berries

I've modified the code somewhat based on the actual structure of your worksheets

Please Login or Register  to view this content.

A couple of things...the code assumes your data starts in Column J as you inferred in your original post. However, in the workbook you posted, data appears to start in Columns J, G and H. Is this the case or am I looking at it wrong?

Second thing...the code halts at

Please Login or Register  to view this content.

That's why these lines are commented out. Your named range "Code Required" appears to be empty.

I'm not sure what you're trying to do here but the code does what it's written to do.

Let me know of issues.

John

99 times out of 100, this line of code will do what you want:

Please Login or Register  to view this content.

But that's not really a reliable use of UsedRange function. There's a hidden flaw most people won't run into, and when they finally do they won't understand what's gone wrong.

UsedRange.Columns.Count means "tell me how many columns have data", not "tell me what the last column is".

Using the function the way you're using it is only accidentally giving you the correct answer. More simply, if you skip any rows at the top of your worksheet or columns on the left, the code may no longer give the correct answer.

You want to see this problem in action...

1) Open a blank worksheet
2) Put some data in C3 and no where else

Now try your command code and you'll see that it reports the Last Row and/or the Last Col is 1 instead of the expected 3.

Because of this, I never rely on the USEDRANGE.COLUMNS.COUNT method.

===========
This has been a public service announcement brought to you by Calgon...."take me away"

Hi JBeaucaire

Thanks for the "slap upside the head".

I've read of the caveats using

Please Login or Register  to view this content.

and I appreciate your feedback. What I was attempting to do here was count the number of Columns on Row 1 that have data beginning in Column 10 to the right. This didn't work

Please Login or Register  to view this content.

it gave me a false answer . Also, this didn't work

Please Login or Register  to view this content.

After a couple of hours of not being able to figure out why, I resorted the "offending" code.

You may or may not believe that I've rewritten this post because, in writing the original version, I had an "Aha" moment. "Right" vs "Left"? Sometimes I'm dyslexic like that.

Thanks again for your feedback.

John

Here are my two favorite techniques for spotting the last column accurately. The first YOU pick which row to look across, the second simply finds the last column of data by examining all columns.

Please Login or Register  to view this content.

John, as you guys continue to work with this macro, I believe your results will improve if you can edit out all the "selections" and simply address your commands to the rows/columns directly.

Hi Berries

Try this code; it appears to be doing as it should.

Please Login or Register  to view this content.

Please note, I had to unprotect the sheets to make some things work. I didn't set protection back on. You may wish to do that. Let me know of issues.

John

Another option you can use instead of unprotecting a worksheet, you can update the existing protection to ADD the ability for your macro to run freely even though the sheet is protected. The UserInterfaceOnly parameter makes it so the protection only applies to humans, not to VBA.

Please Login or Register  to view this content.

Use this at the top instead of unprotecting.


NOTE: The UserInterfaceOnly setting disappears when you close the workbook, it must be set at least once again when the workbook is opened again, or just include it in your macros as shown.

Hi JB

Actually, Berries has this workbook open code

Please Login or Register  to view this content.

so I'm not certain why I had issues with some of the code not executing properly. Unprotecting the worksheet fixed the problems I was having so I ran with that.

I'm fairly certain we're not done yet so I'll try to resolve the issue in the next rewrite.

Thanks for your feedback.

John

Hi Tony

I found a bit of a bug. The procedure is picking up an extra record for each sheet. I believe I know the fix. I need you to look the data over carefully and see if you find other issues.

Change this line of code

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

and this should fix the bug I found.

I'm going to be traveling this week and weekend, so I may or may not get time to look at this. But get back to me with issues you see.

John

Hi Berries

I'll try to look at this in the next day or two. My children and grand children are coming to visit for the Holiday so I may not get right on it.

You might show me an example of this

have the name of the respective source worksheet pasted into the 3rd column

I'll get back to you.

John

Hi John

Column A is populated with a combination of:

Please Login or Register  to view this content.

and later:

Please Login or Register  to view this content.

Column B is populated with (Subject to improvement per my previous post):

Please Login or Register  to view this content.

What I am hoping for is a way to also have column C populated with the name of the worksheet (eg. A category materials 1 or B category materials 1) that the data in each row has originated from. The reason for this is so that I can use formulae to extract the additional information that I need from each of the relevant worksheets.

I hope that this makes sense.

Thanks John, and have a great time with your family. I will also be heading north on the weekend to see my family- they are taking me away for a couple of days so I will be offline until Tuesday night my time.

Hi Berries

See if this code solves the problem you were having and if it satisfies regarding the file name in column C

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Let me know of issues.

John

Originally Posted by snb

Avoid using select and activate in VBA-code.

Agreed.

Originally Posted by snb

Use Usedrange to determine the number of used columns/rows.

I can never recommend this technique. There is a hidden flaw in the UsedRange method that will eventually bite you.

To see the flaw in action, open a new workbook, put a value in cell F5 only, then try your UsedRange technique to get the LastRow with data, you will get an answer of 1 instead of 5. This means your macro can be failing and you don't even know it.

Simplest way to always get the last row is to examine the column in question specifically, or do a generic "search all rows for the last row of data"...here are the two I always use now:

Please Login or Register  to view this content.

Originally Posted by snb

Use specialcells(2) to identify cells with values in it.

If you want people to have no clue what your code is doing at this point, use SpecialCells(2). If you want them to at least be able to read it the first time through without wondering, use SpecialCells(xlConstants).

There are a LOT of shortcuts to make code take fewer letters and people use them, but they typically make the code a LOT more difficult to understand. Since this is a teaching forum, I use them less and less every day.

Originally Posted by snb

I think you don't need autofilter.

AutoFilter is almost always faster at collecting data than going row by row or cell by cell.

Originally Posted by snb

Most of the time 'copying' values can be done more easily (and faster)
with e.g. range("K2")=range("B7"). (to copy cell B7 to cell K2)

Absolutely.

Hi Berries
Regarding this

Please Login or Register  to view this content.

the line of code clears the clipboard of data. I've always been of the impression that you only needed the line of code once in the procedure to clear the clipboard; however, since you asked the question, I researched it further and can no longer find that reference. In fact, most advise that you include the line of code after each copy/paste action.

Glad the code works for you. Thanks for the "Style" points.

John