Moving Data from main spreadsheet to respective worksheets

[pelbert]

I have a task to complete but I a not sure how to create this action. I am pretty sure this will be have to be done in VB, however, if there is a formula that can be used, I would be grateful to look at this as well.

What I have is a spreadsheet with 27 columns and approximately 5K rows. Row amounts change each month based on sales. The sales are usually in all 50 states.

I have created worksheets for each state. The worksheets are named by the states abbreviation (ex: Florida would be FL, Georgia would be GA, etc).
In order to get the sales in each state into their respective worksheet, I am having to sort the spreadsheet by state and then copy and paste the information into each state’s worksheet.

What I am looking for is to be able to use VB to do this for me by clicking on a button that would move each states data into their respective worksheet.

Is this something that can be done? Any suggestions would be helpful.

I am including a great reduced sample spreadsheet to help make this request more understandable. Column Q is what I use for the State reference.
Thank you ahead of time for your help.

[Marc L]

The easy way is to use a filter or better an advanced filter like any Excel beginner operating manually
and like the so many VBA samples within this forum threads.

But does it depend on the already created states tabs or any missing tab must be added ?

[MRUTOR]

Hi pelbert,

attached is your file back as a .xlsm. with a small macro in Module1. You can assign this macro to a button or what ever you want. it is doing what you want i hope. In the sample file it is doing it.

[torachan]

Hi.
Macro on yellow button.
No need to set up sheet tabs.
The macro inserts the sheets on the fly with the tabs i.d. as column 17 of the master.
torachan.

[Marc L]

According to the attachment as a VBA beginner starter :

Sub Demo1() 
        Dim S$ 
        Application.ScreenUpdating = False 
    With Range("'April Sales'!A1").CurrentRegion 
           .Columns(17).AdvancedFilter xlFilterCopy, , .Range("AG1"), True 
        While Not IsEmpty(.Range("AG2")) 
            S = .Range("AG2").Text 
            If Not Evaluate("ISREF('" & S & "'!A1)") Then Sheets.Add(, Sheets(Sheets.Count)).Name = S 
           .AdvancedFilter xlFilterCopy, .Range("AG1:AG2"), Sheets(S).[A1:AC1] 
           .Range("AG2").Delete xlShiftUp 
        Wend 
           .Range("AG1").Clear 
    End With 
        Application.ScreenUpdating = True 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[torachan]

@Marc
Certainly very fast - but not very accurate.
torachan.

[pelbert]

Marc L,
Thank you for the help on this. I applied the VB script to the file I supplied and it works just fine. I also applied your same VB script to the original file and it appears to work rather quickly and created all of the worksheets I was needing. However, I did find after looking at each state worksheet, it loaded in the next state's data and the last worksheet loaded the first worksheet data. By this I mean to say that the first worksheet has AL (Alabama) with is correct, however, it had the AZ (Arizona) data in the AL worksheet. Then looking at the AZ (Arizona) worksheet, it had the following data in from the next worksheet name CA (California) and so on....until the end, which is the WY (Wyoming) data sheet...had the AL (Alabama) data in it. I ran this numerous times and came back with the same result everytime.

[pelbert]

torachan,
Thank you for your contribution to my needs on this. I clicked on the button you added to the return file and it all worked perfectly. I then copied the script to my original file clicked f5 and it ran perfectly. My challenge at this time is to learn more about how to install and make the CommandButton1 work. My skills are very basic at this time and have paid a fair amount of money on VB Learning Course to better my skills. I am sure I will figure it out and I am sure it is something rather basic to do. Thank you again for your help with this for it will make a giant difference in how much time I will be spending not having to copy and paste or not having to manually deal with the Advanced Filter. Great job and thank you very much for taking your time helping me with this.

[pelbert]

MRUTOR,
Thank you for contributing to my question at hand. I installed your script on my original file (not the one I posted), but exactly the same in columns, and I could not get it to finish beyond the first folder. I kept getting the runtime error message '9'. Subscript out of range. I am not sure what to correct on this, but the line in question is lngLastState-Sheets(strWWstate).cells.....(this statement is 5 lines from the bottom).

I will continue to try to modify the statement to see if I can make something happen with it.

Thank you again for your jumping right in and trying to help. I really do appreciate it. Take care

Pete

[Marc L]

Certainly very fast - but not very accurate.

however, it had the AZ (Arizona) data in the AL worksheet.

My bad ‼ It was a variation of my original procedure but I was on phone when I posted it too quickly without checking this variation result …
I just have edited my post #5 back to the original procedure well working, try it !

pelbert, thanks for the rep' !

[pelbert]

MarcL
I went back and loaded your script and it works like a champ. Very quick and does exactly what I was looking to do. Thank you for taking the time to go back and re-evaluate your previous response. I really do appreciate it. I will also go over your newest response and evaluate your statements to see exactly what does what and why. Good learning tool for me. Once again...thank you so much. Take care

[Marc L]

The issue was I wanted to use one codeline less but as the criteria must be removed only after the advanced filter
so removing it before was very not a great idea ! It's what may happen when coding during a phone call …