Moving data to seperate sheets and columns

[mtharnden]

hello, i am creating a macro and i have made it far but stuck on the last step...

i have a sheet with a list of data and i am trying to get it moved to sepecific sheets and columns based off the value of another cell in the row.
i cannot post the actual sheet due to the information on it, but i made up an example that is an exact replica of what i need done.

in the example there are columns with Name, State, and City
there are then Tabs with each State and in the sheet there are columns with the cities.
i am trying to move the names to the corrosponding City Column within the correstonding sheet for the state

i cannot find out how to attach a file, so here is an example:

<TABLE style="WIDTH: 196pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=261><COLGROUP><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 64pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=85>Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>State</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 84pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=112>City</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Name 007</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Colorado</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Colorado Springs</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Name 012</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Colorado</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Littleton</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Name 015</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Colorado</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Littleton</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Name 042</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Utah</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>West Valley City</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Name 053</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>California</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>LA</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Name 018</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Colorado</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Englewood</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Name 031</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Arizona</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Tempe</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Name 046</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Utah</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Ogden</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Name 060</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>California</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Compton</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Name 054</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>California</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>LA</TD></TR></TBODY></TABLE>
**note there will be about 8,000 rows of date

Now there will be a sheet called Colorado with headings like this:
<TABLE style="WIDTH: 320pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=426><COLGROUP><COL style="WIDTH: 39pt; mso-width-source: userset; mso-width-alt: 1901" width=52><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1792" width=49><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><COL style="WIDTH: 45pt; mso-width-source: userset; mso-width-alt: 2194" width=60><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 39pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=52>Denver</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 37pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=49>Aurora</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 84pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=112>Colorado Springs</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 58pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=77>Fort Collins</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 45pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=60>Littleton</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 57pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=76>Englewood</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR></TBODY></TABLE>
**note there is a seperate sheet for each tab

now the Sheet names and column heading will match the cells in the main data so however it references or looks up will match.

all i need to move are the names to the correct office column, there ahs to be an easy way

thanks for the help!

 

[jbeaucaire]

This will cycle through the worksheets one at a time and apply a filter to your "Data" sheet column B for the State, then for each city across the top in row 1, it will apply a second filter to your "Data" sheet column C for the cities... if any rows are left visible the names from column A will be copied to that state sheet city column.

It will repeat for every sheet, except the one called "Data" which has thie table you described.

Option Explicit

Sub ParseData()
Dim ws As Worksheet
Dim LR As Long
Dim City As Range

On Error Resume Next

With Sheets("Data")
    .AutoFilterMode = False
    .Rows(1).AutoFilter
    
    For Each ws In Worksheets
        If ws.Name <> .Name Then
            .Rows(1).AutoFilter Field:=2, Criteria1:=ws.Name
            
            For Each City In ws.Rows(1).SpecialCells(xlConstants)
                .Rows(1).AutoFilter Field:=3, Criteria1:=City
                LR = .Range("B" & .Rows.Count).End(xlUp).Row
                If LR > 1 Then
                    .Range("A2:A" & LR).Copy
                    ws.Cells(ws.Rows.Count, City.Column).End(xlUp).Offset(1).PasteSpecial xlPasteValues
                End If
            Next City
        End If
    Next ws
End With

End Sub

-


This macro adds the data to the city columns below any existing data.

 

[hiker95]

mtharnden,


My sample workbook only contains two worksheets for testing:

Excel Workbook
	A	B	C
1	Name	State	City
2	Name 007	Colorado	Colorado Springs
3	Name 012	Colorado	Littleton
4	Name 015	Colorado	Littleton
5	Name 042	Utah	West Valley City
6	Name 053	California	LA
7	Name 018	Colorado	Englewood
8	Name 031	Arizona	Tempe
9	Name 046	Utah	Ogden
10	Name 060	California	Compton
11	Name 054	California	LA
12
Main Data

Excel Workbook
	A	B	C	D	E	F
1	Denver	Aurora	Colorado Springs	Fort Collins	Littleton	Englewood
2
3
4
Colorado

After the macro worksheets are added, City's and Names are added, etc.:

Excel Workbook
	A	B	C	D	E	F
1	Denver	Aurora	Colorado Springs	Fort Collins	Littleton	Englewood
2			Name 007		Name 012	Name 018
3					Name 015
4
Colorado

Excel Workbook
	A	B
1	West Valley City	Ogden
2	Name 042	Name 046
3
Utah

Excel Workbook
	A	B
1	LA	Compton
2	Name 053	Name 060
3	Name 054
4
California

Excel Workbook
	A
1	Tempe
2	Name 031
3
Arizona

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub MoveData()
' hiker95, 08/17/2011
' http://www.mrexcel.com/forum/showthread.php?t=572629
Dim wM As Worksheet, ws As Worksheet
Dim c As Range, NR As Long, FC As Long, NC As Long
Application.ScreenUpdating = False
Set wM = Worksheets("Main Data")
For Each c In wM.Range("B2", wM.Range("B" & Rows.Count).End(xlUp))
  If Not Evaluate("ISREF(" & Trim(c) & "!A1)") Then Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = Trim(c)
  Set ws = Worksheets(Trim(c.Value))
  FC = 0
  On Error Resume Next
  FC = Application.Match(c.Offset(, 1), ws.Rows(1), 0)
  On Error GoTo 0
  If FC = 0 Then
    NC = ws.Cells(1, Columns.Count).End(xlToLeft).Column + 1
    If ws.Cells(NC - 1, 1) = "" Then NC = NC - 1
    ws.Cells(1, NC) = c.Offset(, 1)
    NR = ws.Cells(ws.Rows.Count, NC).End(xlUp).Row + 1
    ws.Cells(NR, NC) = c.Offset(, -1)
    ws.Columns(NC).AutoFit
  Else
    NR = ws.Cells(ws.Rows.Count, FC).End(xlUp).Row + 1
    ws.Cells(NR, FC) = c.Offset(, -1)
    ws.Columns(FC).AutoFit
  End If
Next c
wM.Activate
Application.ScreenUpdating = True
End Sub

Then run the MoveData macro.

 

[Peter_SSs]

Welcome to the MrExcel board!

You appear to be saying that all the state sheets already exist as do all the relevant city names on those state sheets. I have assumed that is the case.

Please test in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> MoveNames()<br>    <SPAN style="color:#00007F">Dim</SPAN> CityCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, rws <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, nr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> St <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, Cty <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Data")<br>        <SPAN style="color:#00007F">With</SPAN> .Range("A1", .Range("C" & .Rows.Count).End(xlUp))<br>            rws = .Rows.Count - 1<br>            .Offset(, 1).Resize(, 2).AdvancedFilter Action:=xlFilterCopy, _<br>                CopyToRange:=.Range("J1:K1"), Unique:=<SPAN style="color:#00007F">True</SPAN><br>            <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> .Range("J2", .Range("J" & .Rows.Count).End(xlUp))<br>                St = c.Value<br>                Cty = c.Offset(, 1).Value<br>                <SPAN style="color:#00007F">With</SPAN> Sheets(St)<br>                    CityCol = .Rows(1).Find(What:=Cty, LookIn:=xlValues, _<br>                        LookAt:=xlWhole, MatchCase:=False, SearchFormat:=<SPAN style="color:#00007F">False</SPAN>).Column<br>                    nr = .Cells(.Rows.Count, CityCol).End(xlUp).Row + 1<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                .AutoFilter Field:=2, Criteria1:=c.Value<br>                .AutoFilter Field:=3, Criteria1:=c.Offset(, 1).Value<br>                .Offset(1).Resize(rws, 1).Copy Destination:=Sheets(St).Cells(nr, CityCol)<br>            <SPAN style="color:#00007F">Next</SPAN> c<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        .AutoFilterMode = False<br>        .Range("J1").CurrentRegion.ClearContents<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><br>End <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[jbeaucaire]

I realized I forgot to turn off the Autofilter when I was done:

Option Explicit

Sub ParseData()
Dim ws As Worksheet
Dim LR As Long
Dim City As Range

On Error Resume Next

With Sheets("Data")
    .AutoFilterMode = False
    .Rows(1).AutoFilter
    
    For Each ws In Worksheets
        If ws.Name <> .Name Then
            .Rows(1).AutoFilter Field:=2, Criteria1:=ws.Name
            
            For Each City In ws.Rows(1).SpecialCells(xlConstants)
                .Rows(1).AutoFilter Field:=3, Criteria1:=City
                LR = .Range("B" & .Rows.Count).End(xlUp).Row
                If LR > 1 Then
                    .Range("A2:A" & LR).Copy
                    ws.Cells(ws.Rows.Count, City.Column).End(xlUp).Offset(1).PasteSpecial xlPasteValues
                End If
            Next City
        End If
    Next ws
    .AutoFilterMode = False
End With

End Sub

 

[mtharnden]

JB,
thanks, your code seems to be the one that will do what i need

i have gone into my file and put it in, i did change the word "City" to "CAR" in the code to keep my lables clean to what it actually is called in my file.

it works except one issue, i had a couple cities that did not copy over. like i said it is a lot of data so i did not audit it all, but one that did not go is Chicago.
i checked spelling and formatting and it all seems ok as far as the data is concerned and most the others copied over just fine between the different tabs and columns, and the ones that did go went perfectly.

the whole list of Chicago did not copy over so i think it may have something to do with the filter?
any help is appreciated.

thanks again, and also if you can explain the code by breaking it down to what each part is doing i would love that, i am really trying to learb more in VBA especially with strings.

 

[hiker95]

mtharnden,

Have you tried my macro?

If a city is not in one of the State worksheets, the macro will add the city to the next available column, and then copy the Name to the new column in the next available row.

 

[mtharnden]

mtharnden,

Have you tried my macro?

If a city is not in one of the State worksheets, the macro will add the city to the next available column, and then copy the Name to the new column in the next available row.

Yes I did try it, It crashed at the start.
I like how you have it set up to add the cities, the good thing for me is the cities are consistent and never change.

There will never be new cities and the names within them may change cities or add new names or remove, hence the purpose of a macro.

There will be some cities listed that do not always have names listed. The reason I have it using pre written column headers is due to having to have it match where the data is later moved for a separate macro (this one is a "template builder" that runs once a week to keep all the names up to date).

 

[Peter_SSs]

For completeness, did you try mine?

It appears that Jerry's code loops through every city on every state worksheet and looks to see if there is any of that data on the main sheet, though there may not be any. hiker's code loops through each line of data on the main sheet and moves it. My code attempts to look for the unique combinations of state/city on the main data sheet and move those sets of data in blocks - it seemed like an efficient approach to me.

As I stated in my earlier post, my code does not check if the state sheet or city column exists like hiker's does. I had assumed that this was maybe a company worksheet or similar and the states/cities were known and fixed.

If any code 'crashes' it would be helpful to the writer to know what the exact error message is and what line of code caused it.

 

[jbeaucaire]

I would suspect a hidden "space" on your Colorado sheet after/before the word "Chicago ", or hidden spaces in the "chicago " entries on the data page.

You can highlight that column and do a Search/Replace for "Chicago " and replace with "Chicago", that might clean it up.

Glad mine worked for you outside of that. The other approaches in this thread, all good stuff!