help editing vba to filter data/paste new selection in new sheet

My table includes data associated with a code. These codes are 2 to 6 digits long. I've formatted that column as text and counted the character length in Column A. I've applied a filter to that column and recorded a macro of me selecting the 2-digit code data and pasting it in a new worksheet, then starting over and selecting for the 3-digit code data etc. I"ve attached the practice file to this thread: SepCodeByDigitNumber.xlsm.

The macro is called Sub SeparateCodesByDigitLength() [see attached file], and it's pretty long or I'd insert it here.
I need help editing this code to be more streamlined (I'm sure it could loop etc.). I've done some editing so far but don't want to delete something I shouldn't.

Thank you for your help in advance. Happy New Year! Enjoy the holiday!

Hi burnettech,

See if this code makes sense and makes it all quicker. Run this code while looking at the data sheet.

Please Login or Register  to view this content.

Marvin, thank you so much for your code. It works! Something I need to do, though, is have it name the new sheets created, have the procedure go back to the sheet 2010_11cirpc to clear the filter, and to have the cells in the new sheets unselected/view 75% zoom. I was not sure how to do this with your code, so I tried to adapt another code I have for a different task, but it does not work. I get a subscript out of range error.

I'm attaching a new file containing that procedure called SeparateCodesByDigitLength_v2, along with my original recorded macro SeparateCodesByDigitLength, and your code that I've called CreateCodeSheetsWithoutFormatting. These are all in Module "SeparateCodesByLength". SepCodeByDigitNumber2.xlsm

BTW,your code is much more concise and professional than mine, so I am not complaining whatsoever. I could manually format the new sheets you've generated, and that would be just fine, but I am trying to learn VBA so would like to follow through with refining the code.

I'm going to PM a forum contributor that was involved with a different task of mine previously in this project who is familiar with the code I adapted above, so if you do not want to spend more time on this until I see if that person responds, I understand. Don't want you to waste time that you don't have to on this New Year's Day. Happy Holiday! You've been very helpful!

Hi burnettech,

The way to learn to code is to turn the macro recorder on and do simple little steps and see what the VBA code for that step is.

For example, I don't know what the VBA code is to zoom to 75%, so I turn on the macro recorder and record the macro to zoom. I turn it off and see what has been recorded. It looks like:

ActiveWindow.Zoom = 75

I then take this code and stick it in my code where it is appropriate.

You need to do the same thing with renaming your tabs and unselecting what got pasted.

I hope this helps you learn and discover. I'd hate to give you a quick answer without you trying to learn a little on your own...

Is this what your trying to do?

Please Login or Register  to view this content.

I understand, and thank you for that suggestion. That is why I recorded my complete macro first. I know what the formatting steps are based on that recorded macro:

Please Login or Register  to view this content.

I guess my biggest obstacle is where to place the instructions within a loop, and also when I have to use variables -- declaring, etc. As you know, those instances cannot be recorded. However, I'm collecting examples of macros in some Kindle books I've purchased from Amazon, so at some point the flow of the procedures will come easier.

Thanks for your help!! Have a great holiday!

Or better yet to save the headers formatting use this code

Please Login or Register  to view this content.

Yes! It works. I was soooooooooo close! I didn't realize you can't extend the range ["A1:O8" vs "A1:N8" and .Resize(, 15) vs .Resize (, 14)] to include blank columns without encountering an error. THat makes so much sense. Why didn't I catch that? Now I will add more formatting directions to all the sheets at once here:

Please Login or Register  to view this content.

As I have done before for a different task/different formatting needs:

Please Login or Register  to view this content.

Thank you so much, Mike (and Marvin). I have learned a lot from this exchange!

AH. Just picked up post#7 after already sending premature reply #8. I would not have thought to declare the header as an object defined as a range. That's what I mean about recording macros vs. viewing correct code. The declaration part gets me every time. Thank you so much, again, Mike.

Viewers, Post #7 contains the final code that deems this thread [SOLVED].