Need help on a Macro Excel 2010 for filtering / cut / save worksheet

[excelblackbelt]

Hello:

I have a worksheet that I need to breakout by location into separate worksheets. Does anyone know of a macro that does this? I don't want to have to copy and paste every single country out into new sheets. I am working with 12,000 rows of data. Also, if I am maintaining budgets in these worksheets will it still maintain the countries budget in each separate worksheet. Any help would be greatly appreciated. Please let me know if you have any questions.

Thank you,
Joey

 

[SkipVought]

hi,

What is your purpose for putting each country on a separate sheet?

For instance, there are a number of techniques that enable you, ON ONE SHEET, to display the data for any coutry, using a control of some sort. Would such a simple technique be suitable for your puropses?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[excelblackbelt]

Well for the project I am working with now I have 15 columns of data that I need sorted by country and that has to be parsed out into individual worksheets by country. In the future I will have the same file with budgets attached to each country for each person. I just need the former Macro right now. Thanks for the response Skip!

 

[SkipVought]

So each sheet is going to be sent to a separate person for future processing, or information only?

And you don't care at all about the original table of data that you will just chop up and distribute?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[excelblackbelt]

The different sheets will be cut out by country for their respective information. I can copy the master file and with the copy divide using the macro...I hope this helped.

 

[SkipVought]

Here's what you can do.

I assume that you get new data table periodically that you must then distribute. How that update happens may be a signicant issue later. SaveAs your workbook as a Macro-Enabled workbook.

Let's assume that Sheet1 has your source table.

On a new sheet query the source table using Data > Get External Data > From other Sources > From Microsoft Query -- and drill down to your workbook. You will query Sheet1$ and return the COUNTRY using View > Query Properrties - Unique values only. And then File > Return data to Microsoft Excel. This need to be done ONE TIME ONLY. Subsequent queries, as you get new data, will simply be a REFRESH of the QueryTable you have added to Sheet2.

This produces a list of Countries, from which you will drive your application.
[pre]
Turn ON the Macro recorder.

On Sheet1, set the autofilter and filter ONE conutry.

COPY the vuisible data

Add a workbook and PASTE the results in A1 of Sheet1 of the new workbook.

SAVE the new workbook and CLOSE

Turn Off the Macro Recorder.
[/pre]
Post back with your recorded code to get help in customizing.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[excelblackbelt]

Hey Skip,

Here you go...

Sub CuttingOutWorkSheets()
'
' CuttingOutWorkSheets Macro
'

'
Selection.AutoFilter
ActiveSheet.Range("$A$1:$FO$349").AutoFilter Field:=5, Criteria1:= _
"China-Yuan"
Cells.Select
Selection.Copy
Sheets.Add After:=ActiveSheet
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
, SkipBlanks:=False, Transpose:=False
Cells.Select
Cells.EntireColumn.AutoFit
Cells.EntireColumn.AutoFit
Selection.ColumnWidth = 9
Cells.Select
Cells.EntireColumn.AutoFit
Range("A2").Select
End Sub

Now it only does the same country...but I need it to do every country...and copy a new worksheet!

Thank You!

 

[SkipVought]

Sub CuttingOutWorkSheets()
'
' CuttingOutWorkSheets Macro
'
   Dim r as range
'Assumes country list on Sheet2, list heading in A1 & table on Sheet1...
   With Sheet2
      For each r in .Range(.Cells(2,"A"), .Cells(.Cells(2,"A").end(xldown)))
         With Sheet1
             .Range("$A$1:$FO$349").AutoFilter Field:=5, Criteria1:=r.Value
             .UsedRange.SpecialCells(xlCellTypeVisible).copy
             With Sheets.Add (After:=Sheet1)
                 .Cells(1,1).PasteSpecial Paste:=xlPasteAllUsingSourceTheme
                 .Cells.EntireColumn.AutoFit
                 .Cells(2,1).Select
             End With
         End With
      Next
   End With

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[excelblackbelt]

I got a compile error...

 

[SkipVought]

on what statement?

Do you have an End Sub?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[excelblackbelt]

This line is bad in the Debugger:

[highlight #000000] For each r in .Range(.Cells(2,"A"), .Cells(.Cells(2,"A").end(xldown)))[/highlight][highlight #FCE94F][/highlight]

 

[SkipVought]

Please post all your code.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[excelblackbelt]

CODE
Sub CuttingOutWorkSheets()
'
' CuttingOutWorkSheets Macro
'
Dim r as range
'Assumes country list on Sheet2, list heading in A1 & table on Sheet1...
With Sheet2
For each r in .Range(.Cells(2,"A"), .Cells(.Cells(2,"A").end(xldown)))
With Sheet1
.Range("$A$1:$FO$349").AutoFilter Field:=5, Criteria1:=r.Value
.UsedRange.SpecialCells(xlCellTypeVisible).copy
With Sheets.Add (After:=Sheet1)
.Cells(1,1).PasteSpecial Paste:=xlPasteAllUsingSourceTheme
.Cells.EntireColumn.AutoFit
.Cells(2,1).Select
End With
End With
Next
End With


Skip - I was just wondering do you do any Freelance VBA work?

 

[SkipVought]

Sorry, my range syntax was incorrect

Sub CuttingOutWorkSheets()
'
' CuttingOutWorkSheets Macro
'
    Dim r As Range
    'Assumes country list on Sheet2, list heading in A1 & table on Sheet1...
    With Sheet2
        For Each r In .Range(.Cells(2, "A"), .Cells(2, "A").End(xlDown))
            With Sheet1
                .Range("$A$1:$FO$349").AutoFilter Field:=5, Criteria1:=r.Value
                .UsedRange.SpecialCells(xlCellTypeVisible).Copy
                With Sheets.Add(After:=Sheet1)
                    .Cells(1, 1).PasteSpecial Paste:=xlPasteAllUsingSourceTheme
                    .Cells.EntireColumn.AutoFit
                    .Cells(2, 1).Select
                End With
            End With
        Next
    End With

End Sub
[code]

And no I presently do not. However, I am retiring in a few months and who know if I might do some freelancing. I will certainly be on Tek-Tips regularly.

Skip,
[sub]
[glasses]Just traded in my [b]OLD subtlety[/b]...
for a [b]NUance![/b][tongue][/sub]

 

[Gavona]

An alternate approach is to use pivottables ShowPages feature. It is really quick! You can even consider using that and then copying each sheet to values - this converts from a pivot table to a normal file.

http://www.tek-tips.com/viewthread.cfm?qid=1710919

Gavin

 

[excelblackbelt]

Hi Skip,

Can I send you the master worksheet (w/o the data) that I have to be cut by pool manager? I need to copy out 35 worksheets and do not want to do it manually and it has to maintain the formulas. The Column header I need to cut off the master is called Pool Member (not country). Can I use this same macro?

Thank you,
Joey

 

[SkipVought]

Send me a message using this link faq707-4594

I will reply to you via eMail.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[excelblackbelt]

Just did!

 

[SkipVought]

So lets review what you intend to occur:

[pre]
For each Name in Pool Owner

Insert a New Sheet into your workbook

Filter on Name in master

COPY the data on the master

PasteSpecial into the New Sheet for visible cells/Formulas

Next
[/pre]
Is this it?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[excelblackbelt]

Can I do that with a macro?