Excel VBA problem

[grego9]

--------------------------------------------------------------------------------

I have a bit of visual basic code in an excel spreadsheet that I need some help with. I am attempting to search a file called "TO Cancellations2.xls" for names and for each name that it finds that matches the list of names on the "counterparties" sheet of a file called Time Option Template WIP.xls I want the whole line copied and pasted into a new excel worksheet that should have the counterparty name and then FXTOX.xls after it.

The TO Cancellation2.xls file looks like this

Counterparty
Oy AB 109385 H2 07
Oy AB 109389 H1 08
Oy AB 109390 H2 08
Oy AB 109387 H2 07
Oy AB 109388 H1 08
AB (Sweden) 109391 H2 08

The code below works ok but if the TO Cancellations2.xls file has the same counterparty more than once (as in this case with 5 different records against Oy AB) then instead of saving all 5 records into one file it overwrites the file each time with each of these 5 instances - only keeping the last record. Can anyone suggest any ideas on how to alter this code so that if there is more than one row countaining the same counterparty name it saves all of the rows to the same file and doesn't keep overwriting like it is currently doing?


Sheets("Counterparties").Select

' Initialise Counter
Countz = 0
For Each c In Range("A1:A100")
If c.Value = "" Then
Exit For
Else
Countz = Countz + 1
End If
Next c

Sheets("Data2").Select (NB data2 is a sheet in the Time Option Template WIP.xls file)
Range("A1").Select

For Counter = 1 To Countz

Set Matching = Worksheets("Counterparties").Cells(Counter, 1)
Set Matching2 = Worksheets("Counterparties").Cells(Counter + 1, 1)
Set Matching3 = Worksheets("Counterparties").Cells(Counter + 2, 1)
Set Matching4 = Worksheets("Counterparties").Cells(Counter + 3, 1)
Set Matching5 = Worksheets("Counterparties").Cells(Counter + 4, 1)
Set Matching6 = Worksheets("Counterparties").Cells(Counter + 5, 1)
Set Matching7 = Worksheets("Counterparties").Cells(Counter + 6, 1)

For Each c In Range("A1:A5001")

If c.Value = Matching.Value Then
If c.Offset(0, 0).Value = Matching2.Value Or c.Offset(0, 0).Value = Matching3.Value Or c.Offset(0, 0).Value = Matching4.Value Or c.Offset(0, 0).Value = Matching5.Value Or c.Offset(0, 0).Value = Matching6.Value Or c.Offset(0, 0).Value = Matching7.Value Then
Else
c.EntireRow.Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Cells(2, 1).Select
stdocname = "Q:\Dealing Room\Dealing room administration\Admin Things\Batch Report Masters\Outputs\" & Matching.Value & " FXTOX.xls"
ActiveWorkbook.SaveAs Filename:=stdocname _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Windows("Time Option Template WIP.xls").Activate
stdocname = Matching.Value & " FXTOX.xls"
Counti = 2
Do Until c.Offset(Counti, 0).Value = Matching2 Or c.Offset(Counti, 0).Value = Matching3 Or c.Offset(Counti, 0).Value = Matching4 Or c.Offset(Counti, 0).Value = Matching5 Or c.Offset(Counti, 0).Value = Matching6 Or c.Offset(Counti, 0).Value = Matching7 Or c.Offset(Counti, 0).Value = "Contact RG"
c.Offset(Counti, 0).EntireRow.Select
Selection.Copy
Windows(stdocname).Activate
Cells(Counti + 1, 1).Select
ActiveSheet.Paste
Windows("Time Option Template WIP.xls").Activate
Counti = Counti + 1
Loop

[ems9tech]

When I have multiple rows that I need in a worksheet, I usually send them to a separate function as a recordset or dictionary item (you could use a collection also), and write them to the workbook.

I'm a little confused. Are you trying to write each type of data to a different Worksheet within the same Workbook or does each type of data go into a separate Workbook?

[grego9]

Thanks for the response. I am trying to save all the lines that have the same counterparty against them into the same workbook. The code I currently have takes each line and keeps saving over the previous record (by creating a new workbook each time). I really just the need the code to recognise that there may be more than one line item against each counterparty.

So as an example:

OY AB 545454545
AB SWEDEN 11313131
AB SWEDEN 65648787

I would want both AB Sweden entries saving in the AB Sweden spreadsheet. Currently the last entry saves over the first entry leaving me with just one record on the file

How would I send the items to a separate Function./Recordset - I don't know how to do this

thanks