How to Split A Range of Cells Into Multiple Sheets

[greenarrow]

Hi guys:

I have a task that bothers me since I did it manually.

I download a weather report as CSV file from NOAA(National Oceanic and Atmospheric Administration) that contains every weather stations I select, probably at least 20 stations. Inside of the file, there is only one sheet contains every station. What I did was to manually split the each station to a new worksheet which means I also manually created 20 worksheets. Meanwhile, I needed to name the worksheets by each stations' name "manually". it's important so I double quote.

These are the files how I started and finished. Can anyone provide any opinions?

Thank you

[Marc L]

Hi !

Should be easy using an advanced filter …

[Trebor76]

Hi greenarrow,

Can I ask why do want to split the data as this is rarely the way to go (certainty in terms of efficiency)? You can analyse all the data in the consolidated sheet with filters and SUBTOTAL formulas for individual site(s)?

Robert

[greenarrow]

Hi marc:
so the advanced filter can split into each sheet? I tried the filter and still have to manually create and past.

[greenarrow]

Hi Robert:
Because I need to run the regression model for each station. then to determine which station can most likely cover the city.

[Trebor76]

Because I need to run the regression model for each station. then to determine which station can most likely cover the city.

So you can't do that for the different ranges in the consolidated view? If not there's plenty of code that will split a consolidated view into its separate parts (tabs).

[Trebor76]

If you must split the data, try this (noting my comments):

Please Login or Register  to view this content.

Regards,

Robert

[Marc L]

so the advanced filter can split into each sheet?
I tried the filter and still have to manually create and past.

Try this code using advanced filters :

Sub Demo1() 
                  Dim Rg As Range, Rc As Range, F$, C&, R& 
                  F = ThisWorkbook.Path & "\NOAA_NJ_04182017_Start (1).csv" 
                  If Dir(F) = "" Then Beep: Exit Sub 
                  Application.ScreenUpdating = False 
    With Workbooks.Open(F, , , 2) 
                  Set Rg = .ActiveSheet.UsedRange.Columns 
                 C& = Rg.Count + 2 
             Set Rc = Rg.Cells(1, C).Resize(2) 
                      Rg(2).AdvancedFilter xlFilterCopy, , Rc(1), True 
        For R& = 2 To Rc(1).End(xlDown).Row 
            With .Worksheets.Add(, .ActiveSheet) 
                 .Name = Left(Split(Rg.Cells(R, C).Value, ",")(0), 31) 
                      Rg.Range("A1:B1,F1:H1,K1:Q1").Copy .[A1] 
                      Rg.Cells(R, C).Copy Rc(2) 
                      Rg.AdvancedFilter xlFilterCopy, Rc, .[A1:L1] 
                 .Columns("A:B").AutoFit 
                 .[A2].Select:   ActiveWindow.FreezePanes = True 
            End With 
        Next 
                  Application.DisplayAlerts = False 
                      Rg.Parent.Delete 
                  Application.DisplayAlerts = True 
                 .Worksheets(1).Activate 
    End With 
                  Set Rg = Nothing:   Set Rc = Nothing 
                  Application.ScreenUpdating = True 
End Sub  

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

[greenarrow]

Try this code using advanced filters :

Sub Demo1()
                  Dim Rg As Range, Rc As Range, F$, C&, R&
                  F = ThisWorkbook.Path & "\NOAA_NJ_04182017_Start (1).csv"
                  If Dir(F) = "" Then Beep: Exit Sub
                  Application.ScreenUpdating = False
    With Workbooks.Open(F, , , 2)
                  Set Rg = .ActiveSheet.UsedRange.Columns
                 C& = Rg.Count + 2
             Set Rc = Rg.Cells(1, C).Resize(2)
                      Rg(2).AdvancedFilter xlFilterCopy, , Rc(1), True
        For R& = 2 To Rc(1).End(xlDown).Row
            With .Worksheets.Add(, .ActiveSheet)
                 .Name = Left(Split(Rg.Cells(R, C).Value, ",")(0), 31)
                      Rg.Range("A1:B1,F1:H1,K1:Q1").Copy .[A1]
                      Rg.Cells(R, C).Copy Rc(2)
                      Rg.AdvancedFilter xlFilterCopy, Rc, .[A1:L1]
                 .Columns("A:B").AutoFit
                 .[A2].Select:   ActiveWindow.FreezePanes = True
            End With
        Next
                  Application.DisplayAlerts = False
                      Rg.Parent.Delete
                  Application.DisplayAlerts = True
                 .Worksheets(1).Activate
    End With
                  Set Rg = Nothing:   Set Rc = Nothing
                  Application.ScreenUpdating = True
End Sub  

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

Hi Marc:
Your code worked perfectlly! But somehow it is hard for me to understand. some format you used I am still not familiar with. But thank you though!

[greenarrow]

If you must split the data, try this (noting my comments):

Please Login or Register  to view this content.

Regards,

Robert

Hi Trebor:
Your code works perfectly as well! I will find a time to study your code. And see if there is something I don't understand! Thank you!

[greenarrow]

If you must split the data, try this (noting my comments):

Please Login or Register  to view this content.

Regards,

Robert

Hi greenarrow,

Can I ask why do want to split the data as this is rarely the way to go (certainty in terms of efficiency)? You can analyse all the data in the consolidated sheet with filters and SUBTOTAL formulas for individual site(s)?

Robert

Hi Robert:

I just took a look at the code. There are some parts that I don't quite get it. If you have extra time, would you walk me through how you thought of the code? I want to understand how you created stetp by step. King Regard

[Trebor76]

Hi greenarraow,

The code is in two key parts.

The first creates an unique list of codes from the entries in Col. A (Station Codes) of the first tab in the CSV file. This unique list (array) determines the sheets that need to be created.

The second part loops through each item in the unique list (array), creates a new blank tab, filters the data in the first sheet based on the current item in that array (station code) and then copies the filtered (visible cells) from the first tab in the CSV file to the newly created tab and renames it using the first 31 characters (maximum character number allowed when naming a tab) of the first entry in Col. B (station name).

HTH

Robert

[greenarrow]

Hi greenarraow,

The code is in two key parts.

The first creates an unique list of codes from the entries in Col. A (Station Codes) of the first tab in the CSV file. This unique list (array) determines the sheets that need to be created.

The second part loops through each item in the unique list (array), creates a new blank tab, filters the data in the first sheet based on the current item in that array (station code) and then copies the filtered (visible cells) from the first tab in the CSV file to the newly created tab and renames it using the first 31 characters (maximum character number allowed when naming a tab) of the first entry in Col. B (station name).

HTH

Robert

Hi Robert:
I still don't quite understand the code structure. This is more complex than I thought. Maybe it is because there are some function I haven't seen. Another question: what does it mean "HTH"?

Regards,
Oliver

[Trebor76]

Hope That Helps (which obviously it didn't too much )

Maybe use Marc L's code if you're more comfortable with that solution.

[peterrc]

Hi greenarrow,

This can be done with Pivot Tables.
I created a Pivot Table and used NAME as a Report Filter.
Under "Options" I used "Show Report Filter Page" to automatically create a Pivot Table for each of the NAMES.
Will this work for you ?

I have had to delete most of the "NAMED" Pivot Tablec because the file exceeded the Excel Help Forum 1Mb file size.

Regards

peterrc

[greenarrow]

Hi greenarrow,

This can be done with Pivot Tables.
I created a Pivot Table and used NAME as a Report Filter.
Under "Options" I used "Show Report Filter Page" to automatically create a Pivot Table for each of the NAMES.
Will this work for you ?

I have had to delete most of the "NAMED" Pivot Tablec because the file exceeded the Excel Help Forum 1Mb file size.

Regards

peterrc

Hi Peterrc:

Sorry for the late reply!

I thought about that but I got stuck after creating pivot tables for each station. I still need to see the detailed info though.

GreenArrow

[Marc L]

Hi Marc:
Your code worked perfectlly! But somehow it is hard for me to understand. some format you used I am still not familiar with.

It's the standard Excel inner feature as you can do manually using an advanced filter,
just activating Macro recorder, the beginner way …

[peterrc]

Hi GreenArrow,

If you delete all the "named" Pivot Tables in the attachment in Post #15, then watch the video in the link below.
The video shows you how to use "Show Report Filter Pages" i.e. automatically create Pivot Tables.

Regards

peterrc

https://www.youtube.com/watch?v=djuLhm0roEA

[greenarrow]

Hi Peterrc!

Now I knew how to split the report by using the pivot option. But how can I get to what you show in the file? Mine is not showing by column, instead of by rows.
01.PNG

[peterrc]

Hi GreenArrow,

You need to change the Report Layout:-

Pivot Table Tools > Design > Report Layout > click down arrow and select "Show In Tabular Format".

Regards

peterrc

[greenarrow]

that was what I tried. But I have the different format than yours.01.PNG

[peterrc]

Hi GreenArrow,

You need to remove the Sub Totals:-

Pivot Table Tools > Design > Sub Totals > click down arrow and select "Do Not Show Subtotals ".

If you are still having issues please attach the file.

Regards

peterrc

[greenarrow]

Hi P:

I made it. Thank you for your guidance!!!!

[peterrc]

Hi GreenArrow,

Thanks for the feedback - happy to help.
If you are happy with the outcome please mark your thread as "Solved".

Regards

peterrc