Find Names and copy to a new sheet

[walkerke2]

Hi All,

I have been using and trying to expand on some code that XLD had provided. I was looking to filter out names on a sheet and add them to another new sheet.

As being a newbie I would like to add more code and process other sheets alike.
The code below is giving me error's on Sheet name and range.. See sheet example.


CheersSub Auditor_workflow()
Dim rng As Range
Dim sh As Worksheet
Dim target As Worksheet
Set ActiveSheet.Name = "SMEB_ALL"
Set target = Worksheets.Add
With sh

Set rng = Range(.Range("J1"), .Range("J1").End(xlDown))
.Columns("J:J").AutoFilter Field:=1, Criteria1:="HA*"
rng.EntireRow.Copy target.Range("A1")
.Columns("D:D").AutoFilter
ActiveSheet.Name = "Auditor Workflow"
Columns.Select
Selection.Rows.Autofit
Selection.Columns.Autofit
End With
End Sub

[Bob Phillips]

I don't recall that code specifically, but I can see that that code will not work. It tries to SET a string variable, and sh is not initialised

Let's start from basics. Are you trying to copy a set of specifice data from each sheet to a (new?) target sheet)

[walkerke2]

Hi XLD,

Yes I'm trying to filter the user name and copy the data they have added to a new sheet with a new sheet name of workflow. Within workflow I can then use a new command (Call ) to run another Section of code..
As being new it's prob the long way!!

Cheers

[mdmackillop]

Try something like

Option Explicit
Sub Auditor_workflow()
Dim rng As Range
Dim sh As Worksheet
Dim target As Worksheet

Set target = Worksheets.Add
target.Name = "Auditor Workflow"
Set sh = Sheets("Sheet1")
With sh
.Rows(1).Insert
Set rng = Range(.Range("D1"), .Range("D1").Offset(1).End(xlDown))
.Columns("D:D").AutoFilter Field:=1, Criteria1:=InputBox("Enter filter data") & "*"
rng.EntireRow.Copy target.Range("A1")
.Columns("D:D").AutoFilter
.Rows(1).Delete
End With
target.Columns.AutoFit
End Sub