How do I cut & paste rows to a new workbook based on a changing value? 1

[Kelser]

I receive a sheet each week with the data in the following format. What I have to do is select rows based on the number in col D then move those rows to a new workbook. So all the rows with 10 in the D col go into a new book, then all the rows with 12 in the D col go into another new book etc. However, next time I receive the data, the numbers in col D might be a different set of numbers. By the way, I use Excel 2003. Any help would be very gratefully received as I know very little re VBA.

A B C D E F
txt txt txt 10 txt num
txt txt txt 10 txt num
txt txt txt 12 txt num
txt txt txt 12 txt num
txt txt txt 20 txt num
txt txt txt 20 txt num
txt txt txt 20 txt num

 

[Kelser]

I used your code as follows:
Sub Testing()
'
' Testing Macro
'
Dim r As Range, ws As Worksheet, i As Integer

Set ws = ActiveSheet

i = 1
For Each r In

• 
  ws.[D1].AutoFilter _
  Field:=4, _
  Criteria1:=r.Value
  
  r.CurrentRegion.Copy
  
  With Workbooks.Add
  With .Sheets(1)
  .Paste
  .UsedRange.EntireColumn.AutoFit
  .SaveAs "c:Test" & i & ".xls"
  End With
  .Close
  End With
  i = i + 1
  Next
  End Sub
  
  My filtered table is in a Workbook called Trial.xls on the first sheet named End.
  
  A snapshot of the information looks like the following:
  
  Name Day Product Num TEAM
  Frank Mon 34320627 106
  Mary Tue 34320725 106
  Jane Thur 34365925 106
  Alan Wed 34385904 106
  John Thur 34420016 106
  John Fri 34334681 107
  Jane Mon 34434911 107
  Alan Mon 34489520 107
  Alan Tue 34489521 107
  Mary Tue 34492668 107
  Mary Thur 34493091 107
  Frank Wed 34507942 107
  Frank Wed 34507951 107
  John Wed 34507958 107
  Jane Thur 34552232 107
  John Fri 34471677 108
  Mary Fri 34472232 108
  Mary Mon 34510456 108
  Mary Mon 34357998 108
  Frank Tue 34462312 108
  
  On the second sheet (of the same workbook) which I left with the generic sheet name, Sheet1, on cells D217 I placed my unique list:
  106
  107
  108
  110
  111
  114
  116
  119
  127
  139
  140
  161
  175
  187
  192
  194
  I used the Name Box to call this: List.
  
  When I run the code, 16 Workbooks are created, but each of them contain only the 16 unique values i.e. List
  
  I hope this makes a little more sense!

 

[SkipVought]

[b]
        ws.[D1][/b].CurrentRegion.Copy

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Kelser]

You will be SOOOOoooo glad to know, it worked perfectly! Thank you so much for taking the time with me. I work with applications only, but I have found this fascinating and think I will continue with the learning.

Again, many thanks for all your help.

 

[SkipVought]

Glad to help.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]