Change ws designator
One way:
Dim rSource As Range
Dim rDest As Range
Dim rCell As Range
On Error Resume Next
With ThisWorkbook.Sheets("New IP Office")
Set rSource = .Range(.Cells(1, 4), _
.Cells(.Rows.Count, 4).End(xlUp))
Set rDest = .Parent.Sheets("Sheet2").Cells( _
.Rows.Count, 1).End(xlUp).Offset(1, 0)
End With
On Error GoTo 0
If (Not rSource Is Nothing) And (Not rDest Is Nothing) Then
For Each rCell In rSource
With rCell
If IsNumeric(.Value) Then
.EntireRow.Copy Destination:=rDest
Set rDest = rDest.Offset(1, 0)
End If
End With
Next rCell
End If
In article ,
Jerry Foley wrote:
Hello, Given the following macro:
Sub mastertest()
Dim ws As Worksheet, cell As Range, rng As Range
For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = "Sheet2" Then
For Each cell In ws.Range("D1:D" & ws.Range("D65536").End(xlUp).Row)
If IsNumeric(cell) = True Then
cell.EntireRow.Copy _
Sheets("Sheet2").Range("A65536").End(xlUp).Offset( 1, 0)
End If
Next cell
End If
Next ws
End Sub
How do I edit it to only seach col D only in the spreadsheet tab "New IP
Office" and write the results in Sheet2?
|