E
Edgar Thoemmes
Hi
See thread below from yesterday.
Sorry dont think i explained myself properly.
With the current code i have to manually enter each
different supplier in the input box for it to copy to a
new sheet. What i want is for this to happen automatically
when i run the macro.
The data that is in column T is common to each supplier
reference which is in column H so i would like to name the
new sheet by the value in column t.
Thanks for your help again.
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)
message
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" <[email protected]>
wrote in message
See thread below from yesterday.
Sorry dont think i explained myself properly.
With the current code i have to manually enter each
different supplier in the input box for it to copy to a
new sheet. What i want is for this to happen automatically
when i run the macro.
The data that is in column T is common to each supplier
reference which is in column H so i would like to name the
new sheet by the value in column t.
Thanks for your help again.
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)
message
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
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" <[email protected]>
wrote in message