I have a report on a sheet in excel. The report shows
every invoice to be included in a payment run and has lots
of different data in seperate columns.

The supplier reference is in Column H and i would like to
copy all rows with that supplier reference to a new sheet
and then save that sheet by a specific cell in that sheet.

The could be any number of invoices for each supplier so
the macro will have somehow copy the first row and then
somehow check to see if the next cell is equal to the last.

Does anyone have any ideas on how to do this?

Thanks

Edgar

Edgar,

Here's a macro to do it

Sub CopyInvoices()
Dim sCriteria As String
Dim sOriginal As String
Dim sNew As String

sCriteria = InputBox("Input Supplier Ref to select")
If sCriteria < "" Then
With ActiveWorkbook
sOriginal = .ActiveSheet.Name
.Worksheets.Add After:=.Worksheets(.Worksheets.Count)
.ActiveSheet.Name = sCriteria
sNew = .ActiveSheet.Name
.Worksheets(sOriginal).Activate
With .ActiveSheet
.Rows(1).Insert
.Range("H1").Value = "Test"
.Columns("H:H").AutoFilter Field:=1, Criteria1:=sCriteria
.Cells.SpecialCells(xlCellTypeVisible).Copy
End With
.Worksheets(sNew).Paste
.Worksheets(sNew).Rows(1).EntireRow.Delete
.Worksheets(sOriginal).Rows(1).EntireRow.Delete
End With
End If

Application.CutCopyMode = False

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Edgar Thoemmes" wrote in message
...
I have a report on a sheet in excel. The report shows
every invoice to be included in a payment run and has lots
of different data in seperate columns.

The supplier reference is in Column H and i would like to
copy all rows with that supplier reference to a new sheet
and then save that sheet by a specific cell in that sheet.

The could be any number of invoices for each supplier so
the macro will have somehow copy the first row and then
somehow check to see if the next cell is equal to the last.

Does anyone have any ideas on how to do this?

Thanks

Edgar

Hi

This works well but there are a couple of problems that I
need to sort.

Firstly instead of having an inputbox to name the new
sheet i would like the values to be copied to a new
workbook and to name the workbook by the value in
column "T".

I would also like the macro to loop through the entire
sheet and finish when it gets to the end.

Thanks



-----Original Message-----
Edgar,

Here's a macro to do it

Sub CopyInvoices()
Dim sCriteria As String
Dim sOriginal As String
Dim sNew As String

sCriteria = InputBox("Input Supplier Ref to select")
If sCriteria < "" Then
With ActiveWorkbook
sOriginal = .ActiveSheet.Name
.Worksheets.Add After:=.Worksheets
(.Worksheets.Count)
.ActiveSheet.Name = sCriteria
sNew = .ActiveSheet.Name
.Worksheets(sOriginal).Activate
With .ActiveSheet
.Rows(1).Insert
.Range("H1").Value = "Test"
.Columns("H:H").AutoFilter Field:=1,
Criteria1:=sCriteria
.Cells.SpecialCells
(xlCellTypeVisible).Copy
End With
.Worksheets(sNew).Paste
.Worksheets(sNew).Rows(1).EntireRow.Delete
.Worksheets(sOriginal).Rows
(1).EntireRow.Delete
End With
End If

Application.CutCopyMode = False

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Edgar Thoemmes"
wrote in message
...
I have a report on a sheet in excel. The report shows
every invoice to be included in a payment run and has
lots
of different data in seperate columns.

The supplier reference is in Column H and i would like
to
copy all rows with that supplier reference to a new
sheet
and then save that sheet by a specific cell in that
sheet.

The could be any number of invoices for each supplier so
the macro will have somehow copy the first row and then
somehow check to see if the next cell is equal to the
last.

Does anyone have any ideas on how to do this?

Thanks

Edgar


.

Edgar,

Where in column T is the criteria. it can't be the whole column?


It already processes the entire sheet, the autofilter process will select
all items that match, so I am, not sure what you mean by the second part.
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Edgar" wrote in message
...
Hi

This works well but there are a couple of problems that I
need to sort.

Firstly instead of having an inputbox to name the new
sheet i would like the values to be copied to a new
workbook and to name the workbook by the value in
column "T".

I would also like the macro to loop through the entire
sheet and finish when it gets to the end.

Thanks

Hi Edgar

I use this program to name sheets in VB to the contents of cell A1

Modify as required

--

Kyle