Create new sheet

[Byron_One]

Hi everyone

I am new to VBA and could really do with some help with something.

i have a workbook called Sales.xls which contains 7 worksheets called North, East, West, South, North West, South East and North East. Each sheet contains Sales by region of the area. What I need to do is create some code that looks at the North, East, South and West sheets only and looks at the column Location in these sheets to see if there are any records (rows) containing the value ZZZ (this is an erroneous value). If it finds any of these records then I'd like all of these rows to be copied to a new sheet in the workbook called ZZZ.

Any help would be much appreciated.

BO

[p45cal]

Give us a file or a link to a file to play with.
As you're a new member you may not immediatley be allowed to upload files or supply links; you can get around the latter by missing off the http:// bit of a link.

[jolivanes]

and looks at the column Location in these sheets

What does that mean?

[jolivanes]

Or, if you mean something like this.
Copy both these codes into your Module1.
Try on a copy of your original first in case there is a problem.



Sub Maybe()
Dim shArr, i As Long, ii As Long


If SheetExists("ZZZ", ActiveWorkbook) = False Then Sheets.Add(After:=Worksheets(Worksheets.Count)).Name = "ZZZ"
shArr = Array("North", "East", "South", "West")

For i = LBound(shArr) To UBound(shArr)
With Sheets(shArr(i))

For ii = 1 To .Cells(.Rows.Count, 1).End(xlUp).Row
If WorksheetFunction.CountIf(.Range(.Cells(ii, 1), .Cells(ii, .Cells(ii, Columns.Count).End(xlToLeft).Column)), "ZZZ") > 0 _
Then .Cells(ii, 1).EntireRow.Copy Sheets("ZZZ").Cells(Rows.Count, 1).End(xlUp).Offset(1)
Next ii

End With
Next i
End Sub



Function SheetExists(SName As String, _
Optional ByVal wb As Workbook) As Boolean
'By Chip Pearson
On Error Resume Next
If wb Is Nothing Then Set wb = ThisWorkbook
SheetExists = CBool(Len(wb.Sheets(SName).Name))
End Function

[jolivanes]

Did any of this help?