Macro to work list in filter and save (reposted from MPE misc)

[Rich]

Hi,

(I've reposted here as probably a more appropriate group)

Workbook A contains data relating to 300 towns. Column A contains the town
names.

I need to split workbook A into several workbooks, each containing one towns
data, and named the towns name.

Currently I manually Filter by the town name, run a macro to copy the data
to a new workbook and format it, the manually save by the town name.

I want to include in my macro the running down each town name in the filter,
then running the rest as per the existing macro, but finishing with saving
the new workbook as town name, which is in cell A2.

I cant figure out how to step to the next item in the filter, or how to save
as a filename based on cell contents.

Any help appreciated.

--
Rich

http://www.richdavies.com

http://www.richdavies.com/profit-protection-recruitment.htm

 

[Incidental]

Hi Rich

Something like the code below might help you out. I ran the code from
a userform with a button in the main workbook, the code will copy the
data stored in each row of that workbook, open a new workbook place
the data in that workbook then save it as the value in the first cell
in a predefined folder (C:\Test\ in my case) this folder must exist or
you will get an error. The code is probably not the neatest way
around this but hopefully you can modify it to suit your needs.

Option Explicit
Dim MyCell, MyRng As Range
Dim CopiedC, CopiedRng As Range
Dim CopyArray As Variant
Dim LstRow, LstCol As Integer
Dim i As Integer
Dim NewWkBk As Workbook


Private Sub CommandButton1_Click()

LstRow = [A65535].End(xlUp).Row

Set MyRng = Range("A1:A" & LstRow)

For Each MyCell In MyRng

LstCol = MyCell.End(xlToRight).Column

CopyArray = Range(MyCell, MyCell.End(xlToRight))

Set NewWkBk = Workbooks.Add

Set CopiedRng = Range("A1").Resize(, LstCol)

i = 1

For Each CopiedC In CopiedRng

CopiedC.Value = CopyArray(1, i)

i = i + 1

Next CopiedC

With NewWkBk

.SaveAs ("C:\Test\" & [A1].Value)
'########### change above to your desired folder
##########################
.Close

End With

Next MyCell

End Sub


hope this helps you out

S

 

[Rich]

Hi Rich

Something like the code below might help you out. I ran the code from
a userform with a button in the main workbook, the code will copy the
data stored in each row of that workbook, open a new workbook place
the data in that workbook then save it as the value in the first cell
in a predefined folder (C:\Test\ in my case) this folder must exist or
you will get an error. The code is probably not the neatest way
around this but hopefully you can modify it to suit your needs.

Option Explicit
Dim MyCell, MyRng As Range
Dim CopiedC, CopiedRng As Range
Dim CopyArray As Variant
Dim LstRow, LstCol As Integer
Dim i As Integer
Dim NewWkBk As Workbook


Private Sub CommandButton1_Click()

LstRow = [A65535].End(xlUp).Row

Set MyRng = Range("A1:A" & LstRow)

For Each MyCell In MyRng

LstCol = MyCell.End(xlToRight).Column

CopyArray = Range(MyCell, MyCell.End(xlToRight))

Set NewWkBk = Workbooks.Add

Set CopiedRng = Range("A1").Resize(, LstCol)

i = 1

For Each CopiedC In CopiedRng

CopiedC.Value = CopyArray(1, i)

i = i + 1

Next CopiedC

With NewWkBk

.SaveAs ("C:\Test\" & [A1].Value)
'########### change above to your desired folder
##########################
.Close

End With

Next MyCell

End Sub


hope this helps you out

S

Thanks for that, I used a variation and it works a treat.

 

[Dave Peterson]

You have a response at your other post, too.

Hi,

(I've reposted here as probably a more appropriate group)

Workbook A contains data relating to 300 towns. Column A contains the town
names.

I need to split workbook A into several workbooks, each containing one towns
data, and named the towns name.

Currently I manually Filter by the town name, run a macro to copy the data
to a new workbook and format it, the manually save by the town name.

I want to include in my macro the running down each town name in the filter,
then running the rest as per the existing macro, but finishing with saving
the new workbook as town name, which is in cell A2.

I cant figure out how to step to the next item in the filter, or how to save
as a filename based on cell contents.

Any help appreciated.

--
Rich

http://www.richdavies.com

http://www.richdavies.com/profit-protection-recruitment.htm

 

[Ron de Bruin]

See this example
http://www.rondebruin.nl/copy5.htm#4)