Excel Sheet to separate by Region

[gregtgonzalez]

Hello guys!
I am a novice with excel and trying to learn VBA to assist in project that has landed in my lap.

Here is what i have to do: I need to build an excel workbook that would allow us to assign incoming referrals to the correct region they belong to (assigned by state), while still being able to track the call attempts made.

i.e. all clients that reside in "CA" are in the "Western Region" the region values are already assigned on the spread sheet as the last column and auto populates based on the state information they live in. "FL" is in the Eastern Region "TX" in the central region.

There are Three "Regions"

What I am trying to do is build an excel spread sheet where we input the daily referrals in the "raw data sheet" (sheet 1) and they get disbursed into the appropriate territory tab.

i.e. tab 1 would be raw data, tab 2 would be Western Region, tab 2 Central Region, and tab 3 Eastern region.​

I recorded a Macro that would filter all the items by region and then copy them to the appropriate sheet in the workbook. However, the macro keeps replacing the info that was already brought over, I need the macro to only bring over the new data starting at the next open line in the appropriate territory tab.

Example
Case Number State Territory
Case1234 CA Western
case5678 TX Central
Case9101 FL Eastern

However this is multiplied by 100s of cases, what i would like is for the macro to filter the ones from each respective territory and transfer the values to the correct Territory Tab, while deleting them from the "raw data" tab. I would like to be able to do this multiple times, as we get "raw data" in daily and have the information transferred into the next open row in the territory tab. Then deleting the information from the raw data, as it is no loner necessary.

This is the recorded macro I have so far
Sub westernregion()
'
' westernregion Macro
'


'
Sheets("RAWDATA ").Select
ActiveSheet.Range("$B$1:$T$208").AutoFilter Field:=19, Criteria1:="WESTERN"
Range("B2:T29").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Sheets("WEST REGION").Select
Range("B2").Select
Sheets("RAWDATA ").Select
Selection.Copy
Sheets("WEST REGION").Select
ActiveSheet.Paste
End Sub

 

[vcoolio]

Hello Greg,

Umm.......the code in the sample you supplied works just as it should!

You have six instances of "Western" which are all transferred to the "Western" sheet.
You have one instance of "Central" which is transferred to the "Central" sheet.
You have three instances of "Eastern" which are all transferred to the "Eastern" sheet.
There is one instance without a region which stays put in the Raw Data sheet.

This can be confirmed by checking that the correct state is also transferred to the correct sheet.

Also, it may be a better option to change this line of code:-

Sheet1.Range("A2", Sheet1.Range("Q" & Sheet1.Rows.Count).End(xlUp)).Delete

to

Sheet1.Range("R2", Sheet1.Range("R" & Sheet1.Rows.Count).End(xlUp)).Entirerow.Delete

Keep us posted!

Cheerio,
vcoolio

 

[gregtgonzalez]

VCOOLIO, thank you so much for your help!
The only issue i seem to be having is with this line

Sheets(ar(i)).Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues

this happens, i am assuming because the macro is taking the headers on and copying and pasting them to the other sheet (usually the western sheet for some reason).
any thoughts? I am breaking ou the vba for dummies book too...i really appreciate all of your insight

 

[vcoolio]

Hello Greg,

The code doesn't copy/paste the headings as you already have them in your individual sheets.
Are you receiving any error messages? If so, which one?
Does your data set (in your actual workbook) start in row 2 with the headings in row 1?

Cheerio,
vcoolio.

 

[gregtgonzalez]

Hi Vcoolio,
thank you for your support. in my actual workbook yes the headers are on row 1 and the data starts on row 2.
Does it matter how the data is input? shoudl i copy and past values only? could that be an issue?
for instance right now, insserted the button and pressed it once, but some of the values remain on the raw data tab, which is fine. but when I hit it a second time it takes away the headers and moves them to a different sheet.

 

[gregtgonzalez]

vcoolio does the type of data affect how the macro is running? i am copy and pasting "values" into the Raw data sheet. When i retried the sample you sent me via dropbox, the code works just fine, when copy and past more data from my data set into the sample workbook you provided, it only sorts the original data (not the newly added actual data). at that point that is when the code does something buggy and starts to select the headers and then moves them to another sheet. Anythoughts?

 

[gregtgonzalez]

also would it be possible to instead of sort the data, to have it go through line by line and identify if in Column R it says "western, eastern or Cetnral" it is to be pasted into the correct tab?

 

[vcoolio]

Hello Greg,

I don't know what is going on at your end as everything is working fine on this end.

No, it doesn't matter how the data is input.
You don't need to manually copy/paste values as the code does this (which will leave any formulae intact).
The code doesn't sort anything. It filters the data by region (Column R) and then transfers the data to the relevant sheet.

You may have to upload your actual work book so we can try to assess what the problem is on your end.

Cheerio,
vcoolio.

P.S.: Just an after thought. Do you have hidden sheets in your work book? Do you have any merged cells in any of the data?

 

[gregtgonzalez]

ok so here is the workbook i am working with, when you hit the button it runs and the first run it takes the headers out. I have tried this exact same file on another computer, so you should hopefully see what i am having an issue with
https://www.dropbox.com/s/tn81dcvsdr7o802/gregtgonzalezprojectreviewforupload.xlsm?dl=0

 

[vcoolio]

Hello Greg,

There were a number of issues with the set out of your workbook, in all sheets, including missing headings (including the "Region" column) and formulae in the headings row. Also, the data that you require to be transferred to the individual region sheets is from Columns A:I not A:Q as per your earlier posts with the remaining columns in the individual region sheets to be filled in manually (as far as I can tell from your set out) as a number of the columns have drop down boxes.

I'm not sure what you were trying to do with the variants to my code in some of the modules but I have removed all but three of the modules with my adjusted code (for Columns A:I) in module 1. Following is the code again:-

Sub TransferData()
        Dim ar As Variant
        Dim i As Integer
        Dim lr As Long
        
ar = Array("Western", "Central", "Eastern")
  
Application.ScreenUpdating = False
Application.DisplayAlerts = False
  For i = 0 To UBound(ar)
         Sheet1.Range("R1", Sheet1.Range("R" & Sheet1.Rows.Count).End(xlUp)).AutoFilter 1, ar(i), 7, , 0
         lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
         If lr > 1 Then
         Sheet1.Range("A2", Sheet1.Range("I" & Sheet1.Rows.Count).End(xlUp)).Copy
         Sheets(ar(i)).Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
         Sheet1.Range("R2", Sheet1.Range("R" & Sheet1.Rows.Count).End(xlUp)).EntireRow.Delete
         Sheets(ar(i)).Columns.AutoFit
         End If
    Next i
[R1].AutoFilter
Application.DisplayAlerts = True
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "Data transfer completed!", vbExclamation, "Status"
End Sub

You have a Worksheet_Change code in one of the modules for some reason. It needs to be placed in the relevant sheet module to work.

Following is the link to your work book which I have tidied up:-

https://www.dropbox.com/s/vofhj0gdufu02q5/GregTConzalez(Actual).xlsm?dl=0

Click on the "RUN" button to see it work.

Cheerio,
vcoolio.

 

[gregtgonzalez]

vcoolio,
you. are. awesome! it works beautifully now! thank you so much! i obvioiusly could not have done this without your guidance.
By any chance would you be willing to explain to me what each step is doing? I realize i can go step by step with f8, however i am looking for something a little more indepth.

I am trying to learn the coding however, the vba for dummies books, does not touched on such advanced writings.
Would you be at all interested in providing such guidance?