VBA: NEWB Tring to Break Out a Single Worksheet into Multiple Workbooks & Worksheets....

[Netopia]

Greetings!

I should start off by saying that I've some limited knowledge of VBA in Access, but have never done anything in VBA with Excel before right now. Please be gentle with me.

I have a single workbook that contains a single worksheet. That worksheet has one column (Q, "REORDER") that contains numerous but undefined values. By undefined, I just mean that we don't know ahead of time what they will be. The general format of the column looks like this:

ABC1
ACB1
BAC1
ABC2
XYZ2
ZYZ2
CAB2
JKL3
MLK3
ABC3
CAB3
ZYZ3
LKJ4
JKL4
ACB4....

So, three letters that change every time the data is provided, followed by a single number of 1-9. That number designates what "group" the data belongs to, e.g. all of the 1's belong to "Group1", 2's to "Group2" ... etc. The three letters may or may not exist in more than one group.

Right now we:

• Manually choose all of the records where Right(Q,1)=1. This would get us all of the "Group1" records.
• Then we manually cut and paste these records into a new workbook.
• We then filter (in the new workbook) for each one of the sub-groups (the three letters plus the number) and manually cut and paste these into separate worksheets.
• Each worksheet is named the same as the sub-group that it contains.
• Once all new worksheets have been created, the first worksheet that was pasted is removed and only the separate sub-group worksheets remain.
• We name the new workbook the same as the original workbook but with " Group1" appended to the end.
• Go back to original workbook and repeat the process for each group that exists in this week's data.

It currently takes a bit over a half hour manually, and then counts have to be double checked between the original workbook and each of the other workbooks/worksheets. I'm sure that in VBA this should be able to be accomplished in a minute or so, maybe two.

Not knowing about Excel VBA, I'm having a hard time even getting started. I know I need some variables, and this is what I have so far:

Sub BreakEfforts()    Dim colLetter As String, SavePath As String
    Dim lastValue As String
    Dim wb As Workbook
    Dim i As Long 'Number of Rows
    Dim currentRow As Long
    Dim RawName As String, BuiltName As String
    Dim EffortNo As String
    Dim NewTab As String
        
    
    colLetter = "Q"
    EffortNo = Right(), 1)  'Don't know how to designate the right of Column Q
    RawName = ActiveWorkbook.Name
    BuiltName = Left(RawName, InStr(RawName, ".") - 1) & " Effort " & EffortNo
    Application.ScreenUpdating = False
    
    SavePath = ActiveWorkbook.Path

I know, I've just scratched the surface. And I'm sure that some of those variables need to be declared inside of a For/Next loop, but again, I'm a neophyte.

Any help with this, and suggestions about how I might add an additional worksheet to the original workbook that shows the count of each subgroup in a group and then the group total, both in the original workbooks and then what is in each of the separate workbooks/worksheets to make sure that all records are accounted for.

Also, if it is anyone's opinion that I've simply bitten off more than I can chew for a first Excel VBA project, I understand. This just happens to be the first thing that I thought of that could be improved upon and I thought it would be a nice project to start learning from.

Thanks if you've even taken the time to read this far!

Joe

 

[Netopia]

No one?

 

[Joe4]

Welcome to the Board!

Also, if it is anyone's opinion that I've simply bitten off more than I can chew for a first Excel VBA project, I understand. This just happens to be the first thing that I thought of that could be improved upon and I thought it would be a nice project to start learning from.

In my opinion, it is a rather ambitious ask for a VBA neophyte. I would recommend picking up a book on learning Excel VBA before tackling something like this. There are some very important things you will need to learn, like looping.

If you do not have the time to learn VBA right now, but really need to get something done, and aren't finding any takers willing to do something for free, you may want to look in to enlisting the help of a consultant. See here: https://www.mrexcel.com/consulting-services/

 

[Netopia]

Well, I decided to dive in and do some learning. The roadblock that I'm currently scratching my head over is how to select a range of rows based on the values in a column.

Let's say I've got 10 columns, with the final column being "Effort". I've already sorted the data by the Effort column (column T in this case) and moved the active cell to T1. Now I want to select all rows, that contain a value of "1" in column T. I can select any row, but what would the best way be to select the range?

I was thinking about a counter loop, counting the occurances of "1", but that doesn't necessarily help with values 2-9. What are people's thoughts on the most reasonable way to do this?

Joe

 

[Joe4]

If your plan is to ultimately copy and paste them to a new workbook, I would look at using Filters to select all the values of 1.
The Advanced Filter option let's you apply it, and copy the results to a new location.

See: https://www.contextures.com/xladvfilter01.html

 

[Netopia]

!!!! Simple! Why didn't that occur to me? I guess I was trying to way overthink it.

Thanks MrExcel! I'm learning quite a bit about VBA with Excel just from this one little project...though I know I've barely put my toe in the water.

Joe

 

[Joe4]

You are welcome!

We all have to start somewhere. Sounds like you are well on your way on your journey!