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]

yes

You will need to create a list is unique names in your Pool Owner column. That list will be the source for your For...Next loop.

Macro record each step separately and modify to generalize it.

Post back with your recorded code for help



Skip,

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

 

[excelblackbelt]

Sub Pool_Member()
'
' Pool_Member Macro
'

'
Rows("14:14").Select
Selection.AutoFilter
ActiveWindow.SmallScroll ToRight:=4
ActiveSheet.Range("$A$14:$BN$426").AutoFilter Field:=13, Criteria1:= _
"Barna, Sandor"
Sheets("2014 Merit Tool").Select
Sheets("2014 Merit Tool").Copy Before:=Sheets(4)
Sheets("2014 Merit Tool (2)").Select
Sheets("2014 Merit Tool (2)").Name = "2014 Merit Tool (2)"
Range("M18").Select
ActiveCell.FormulaR1C1 = "Barna, Sandor"
Sheets("2014 Merit Tool (2)").Select
Sheets("2014 Merit Tool (2)").Name = "2014 Merit Tool Barna, Sandor"
Range("L439").Select
Sheets("2014 Merit Tool Barna, Sandor").Select
Sheets("2014 Merit Tool Barna, Sandor").Copy
ActiveWindow.SmallScroll Down:=-54
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\joliveira\Desktop\Barna, Sandor.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
End Sub

Here is the code I came up with. I wanted the macro to breakout by Pool Member (you have the file so you could make the source list - I would do it but don't know where to place it). Also, I want it to copy out and save by the pool members name to my desktop. I am new to VBA and am not really good at looping...(help!)

Thank you,
Joey

 

[SkipVought]

Where is you LIST?

Your basic control structure is a For...Next loop on your LIST!

Skip,

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

 

[excelblackbelt]

Can I call you?

 

[SkipVought]

This is not rocket science.

Select the Pool Owner heading and data

Paste into a new sheet

Data > Remove Duplicates

Select the heading and data in the new sheet

Formulas > Definef Names > Create from selection > Create names in TOP row
This creates a named range of your unique pool owners

Now you have a list to loop thru

dim r as range

for each r in [Pool_Owner]

next

Skip,

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

 

[SkipVought]

Sorry....

Select the Pool Owner heading and data [highlight #FCE94F]and COPY[/highlight]

Skip,

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

 

[excelblackbelt]

Ok I did that reference. Now where does that go in the code?

 

[SkipVought]

[pre]
For each Name in Pool Owner

Filter on Name in master

COPY the data on the master

Insert a New Sheet into your workbook

PasteSpecial into the New Sheet for visible cells/Formulas

Next

[/pre]

Skip,

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

 

[excelblackbelt]

Thank you for your help.