Excel- Copy and paste data to new workbook

[wayneryeven]

Hi guys
Hoping someone can help. Basically i have a workbook containing 1 worksheet called 'Sheet1'. Data is contained in columsn A: BD. In Cell A there is a Region listed, and what i need to do is split out into a new workbook the rows pertaining to each region. The region is sorted in Region order.

An example of data would be:
Row 1,Column A: Bradford
Row 2,Column A: Bradford
Row 3,Column A: Chicago

I would like to create two additional workbooks, with file names of Bradford and Chicago. The Bradford file would contain the first two records, the Chicago file would contain one record.

Can anyone point me in the direction of how best to achive this?

Regards
wdv

 

[wayneryeven]

For anyone coming back to this at a later date, heres my final code I used to complete the task. Few variables to change.

Sub CopyToWorkbook()
Dim c As Range
Dim rng As Range
Dim r As Long
r = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Application.DisplayAlerts = False
For Each c In Range("NameList")
Range("BM1").Value = c.Value
Range("BE2").Select
Range("BE2").Formula = "=IF(B2=$BM$1,TRUE,"""")"
Range("BE2").AutoFill Destination:=Range("BE2:BE" & r)
Range("A1:BE1793").Sort Key1:=Range("BE2"), Order1:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Columns("BE:BE").Copy
Columns("BE:BE").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Set rng = Columns("BE:BE").SpecialCells(xlCellTypeConstants, 4)
rng.Offset(-1, -56).Resize(rng.Rows.Count + 1, 56).Copy
Workbooks.Add
ActiveSheet.Paste
Range("A1").Select
ChDir "C:\temp"
ActiveWorkbook.SaveAs Filename:="C:\temp\" & c.Value, FileFormat:=xlExcel8, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Close
Next c
Columns("BE:BE").Clear
Range("BM1").Clear
Application.DisplayAlerts = False
End Sub

 

[SkipVought]

Hi,

In the future, please post VBA questions in forum707.

This is one statement I would change after a casual view...

Range("A1").CurrentRegion.Sort Key1:=Range("BE2"), Order1:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

This references the entire table regardless of the number of rows or columns, rather than a fixed range.

I would also explicitly reference the sheet object in EVERY instance of range object.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]