Copy entire row if....
 

Hi Folks!

I've searched the archives and have found a million similar posts but I
don't know enough VBA to adapt what I found to meet my needs.

Comparing uneven ranges on 2 different sheets (Sheet1, Sheet2) looking for
uniques. I'm using a helper column (AE) in Sheet2 that identifies the
uniques by returning the row number if it's a unique or a formula blank ("")
if it's a dupe.

All rows that are identified as unique on sheet2 I would like to have that
ENTIRE ROW copied to another sheet starting in A2. Row 1 in the new sheet
are the headers.

I can do this easily with formulas but this would require way too many.

So, the basic logic is:

If any cells in column AE on Sheet2 contain a numeric value then copy that
ENTIRE ROW to Sheet3 starting in cell A2 on down.

Thanks!

Biff

Copy entire row if....
 

Hi Biff,

Try:
'==========
Public Sub TestIt()
Dim srcRng As Range
Dim destRng As Range

Set destRng = Sheets("Sheet3").Range("A2")

On Error Resume Next
Set srcRng = Sheets("Sheet2").Columns("AE"). _
SpecialCells(xlCellTypeFormulas, xlNumbers)
On Error GoTo 0

If Not srcRng Is Nothing Then
srcRng.Copy Destination:=destRng
End If

End Sub
'<<==========

---
Regards,
Norman



"Biff" wrote in message
...
Hi Folks!

I've searched the archives and have found a million similar posts but I
don't know enough VBA to adapt what I found to meet my needs.

Comparing uneven ranges on 2 different sheets (Sheet1, Sheet2) looking for
uniques. I'm using a helper column (AE) in Sheet2 that identifies the
uniques by returning the row number if it's a unique or a formula blank
("") if it's a dupe.

All rows that are identified as unique on sheet2 I would like to have that
ENTIRE ROW copied to another sheet starting in A2. Row 1 in the new sheet
are the headers.

I can do this easily with formulas but this would require way too many.

So, the basic logic is:

If any cells in column AE on Sheet2 contain a numeric value then copy that
ENTIRE ROW to Sheet3 starting in cell A2 on down.

Thanks!

Biff

Copy entire row if....
 

Hi Biff,

srcRng.Copy Destination:=destRng

Should be:

srcRng.EntireRow.Copy Destination:=destRng

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Biff,

Try:
'==========
Public Sub TestIt()
Dim srcRng As Range
Dim destRng As Range

Set destRng = Sheets("Sheet3").Range("A2")

On Error Resume Next
Set srcRng = Sheets("Sheet2").Columns("AE"). _
SpecialCells(xlCellTypeFormulas, xlNumbers)
On Error GoTo 0

If Not srcRng Is Nothing Then
srcRng.Copy Destination:=destRng
End If

End Sub
'<<==========

---
Regards,
Norman



"Biff" wrote in message
...
Hi Folks!

I've searched the archives and have found a million similar posts but I
don't know enough VBA to adapt what I found to meet my needs.

Comparing uneven ranges on 2 different sheets (Sheet1, Sheet2) looking
for uniques. I'm using a helper column (AE) in Sheet2 that identifies the
uniques by returning the row number if it's a unique or a formula blank
("") if it's a dupe.

All rows that are identified as unique on sheet2 I would like to have
that ENTIRE ROW copied to another sheet starting in A2. Row 1 in the new
sheet are the headers.

I can do this easily with formulas but this would require way too many.

So, the basic logic is:

If any cells in column AE on Sheet2 contain a numeric value then copy
that ENTIRE ROW to Sheet3 starting in cell A2 on down.

Thanks!

Biff

Copy entire row if....
 

Hi Biff

Try:

Sub test()
Dim eRow As Long
Dim i As Long
Dim CpyRow As Long

CpyRow = 2
With Sheets("Sheet2")
eRow = .Cells(Rows.Count, "AE").End(xlUp).Row
For i = 2 To eRow 'starts in row 2 assuming headings in row 1
If .Cells(i, "AE").Value < "" Then
.Rows(i).Copy Sheets("Sheet3").Cells(CpyRow, 1)
CpyRow = CpyRow + 1
End If
Next i
End With
End Sub

Hope this helps
Rowan

Biff wrote:
Hi Folks!

I've searched the archives and have found a million similar posts but I
don't know enough VBA to adapt what I found to meet my needs.

Comparing uneven ranges on 2 different sheets (Sheet1, Sheet2) looking for
uniques. I'm using a helper column (AE) in Sheet2 that identifies the
uniques by returning the row number if it's a unique or a formula blank ("")
if it's a dupe.

All rows that are identified as unique on sheet2 I would like to have that
ENTIRE ROW copied to another sheet starting in A2. Row 1 in the new sheet
are the headers.

I can do this easily with formulas but this would require way too many.

So, the basic logic is:

If any cells in column AE on Sheet2 contain a numeric value then copy that
ENTIRE ROW to Sheet3 starting in cell A2 on down.

Thanks!

Biff

Copy entire row if....
 

Hi Norman!

That only copies the numeric values from column AE. I need the ENTIRE ROW
copied.

Well, not the ENTIRE row but the range An:AAn.

Thanks!

Biff

"Norman Jones" wrote in message
...
Hi Biff,

Try:
'==========
Public Sub TestIt()
Dim srcRng As Range
Dim destRng As Range

Set destRng = Sheets("Sheet3").Range("A2")

On Error Resume Next
Set srcRng = Sheets("Sheet2").Columns("AE"). _
SpecialCells(xlCellTypeFormulas, xlNumbers)
On Error GoTo 0

If Not srcRng Is Nothing Then
srcRng.Copy Destination:=destRng
End If

End Sub
'<<==========

---
Regards,
Norman



"Biff" wrote in message
...
Hi Folks!

I've searched the archives and have found a million similar posts but I
don't know enough VBA to adapt what I found to meet my needs.

Comparing uneven ranges on 2 different sheets (Sheet1, Sheet2) looking
for uniques. I'm using a helper column (AE) in Sheet2 that identifies the
uniques by returning the row number if it's a unique or a formula blank
("") if it's a dupe.

All rows that are identified as unique on sheet2 I would like to have
that ENTIRE ROW copied to another sheet starting in A2. Row 1 in the new
sheet are the headers.

I can do this easily with formulas but this would require way too many.

So, the basic logic is:

If any cells in column AE on Sheet2 contain a numeric value then copy
that ENTIRE ROW to Sheet3 starting in cell A2 on down.

Thanks!

Biff

Copy entire row if....
 

Hi Biff,

As per my followup post,

srcRng.Copy Destination:=destRng

Should be:

srcRng.EntireRow.Copy Destination:=destRng

---
Regards,
Norman



"Biff" wrote in message
...
Hi Norman!

That only copies the numeric values from column AE. I need the ENTIRE ROW
copied.

Well, not the ENTIRE row but the range An:AAn.

Thanks!

Biff

"Norman Jones" wrote in message
...
Hi Biff,

Try:
'==========
Public Sub TestIt()
Dim srcRng As Range
Dim destRng As Range

Set destRng = Sheets("Sheet3").Range("A2")

On Error Resume Next
Set srcRng = Sheets("Sheet2").Columns("AE"). _
SpecialCells(xlCellTypeFormulas, xlNumbers)
On Error GoTo 0

If Not srcRng Is Nothing Then
srcRng.Copy Destination:=destRng
End If

End Sub
'<<==========

---
Regards,
Norman



"Biff" wrote in message
...
Hi Folks!

I've searched the archives and have found a million similar posts but I
don't know enough VBA to adapt what I found to meet my needs.

Comparing uneven ranges on 2 different sheets (Sheet1, Sheet2) looking
for uniques. I'm using a helper column (AE) in Sheet2 that identifies
the uniques by returning the row number if it's a unique or a formula
blank ("") if it's a dupe.

All rows that are identified as unique on sheet2 I would like to have
that ENTIRE ROW copied to another sheet starting in A2. Row 1 in the new
sheet are the headers.

I can do this easily with formulas but this would require way too many.

So, the basic logic is:

If any cells in column AE on Sheet2 contain a numeric value then copy
that ENTIRE ROW to Sheet3 starting in cell A2 on down.

Thanks!

Biff

Copy entire row if....
 

Hi Biff,

That only copies the numeric values from column AE. I need the ENTIRE ROW
copied.

As per my follow-up post:

srcRng.Copy Destination:=destRng

Should be:

srcRng.EntireRow.Copy Destination:=destRng

---
Regards,
Norman



"Biff" wrote in message
...
Hi Norman!

That only copies the numeric values from column AE. I need the ENTIRE ROW
copied.

Well, not the ENTIRE row but the range An:AAn.

Thanks!

Biff

"Norman Jones" wrote in message
...
Hi Biff,

Try:
'==========
Public Sub TestIt()
Dim srcRng As Range
Dim destRng As Range

Set destRng = Sheets("Sheet3").Range("A2")

On Error Resume Next
Set srcRng = Sheets("Sheet2").Columns("AE"). _
SpecialCells(xlCellTypeFormulas, xlNumbers)
On Error GoTo 0

If Not srcRng Is Nothing Then
srcRng.Copy Destination:=destRng
End If

End Sub
'<<==========

---
Regards,
Norman



"Biff" wrote in message
...
Hi Folks!

I've searched the archives and have found a million similar posts but I
don't know enough VBA to adapt what I found to meet my needs.

Comparing uneven ranges on 2 different sheets (Sheet1, Sheet2) looking
for uniques. I'm using a helper column (AE) in Sheet2 that identifies
the uniques by returning the row number if it's a unique or a formula
blank ("") if it's a dupe.

All rows that are identified as unique on sheet2 I would like to have
that ENTIRE ROW copied to another sheet starting in A2. Row 1 in the new
sheet are the headers.

I can do this easily with formulas but this would require way too many.

So, the basic logic is:

If any cells in column AE on Sheet2 contain a numeric value then copy
that ENTIRE ROW to Sheet3 starting in cell A2 on down.

Thanks!

Biff

Copy entire row if....
 

Thanks, Norman!

Biff

"Norman Jones" wrote in message
...
Hi Biff,

As per my followup post,

srcRng.Copy Destination:=destRng

Should be:

srcRng.EntireRow.Copy Destination:=destRng

---
Regards,
Norman



"Biff" wrote in message
...
Hi Norman!

That only copies the numeric values from column AE. I need the ENTIRE ROW
copied.

Well, not the ENTIRE row but the range An:AAn.

Thanks!

Biff

"Norman Jones" wrote in message
...
Hi Biff,

Try:
'==========
Public Sub TestIt()
Dim srcRng As Range
Dim destRng As Range

Set destRng = Sheets("Sheet3").Range("A2")

On Error Resume Next
Set srcRng = Sheets("Sheet2").Columns("AE"). _
SpecialCells(xlCellTypeFormulas, xlNumbers)
On Error GoTo 0

If Not srcRng Is Nothing Then
srcRng.Copy Destination:=destRng
End If

End Sub
'<<==========

---
Regards,
Norman



"Biff" wrote in message
...
Hi Folks!

I've searched the archives and have found a million similar posts but I
don't know enough VBA to adapt what I found to meet my needs.

Comparing uneven ranges on 2 different sheets (Sheet1, Sheet2) looking
for uniques. I'm using a helper column (AE) in Sheet2 that identifies
the uniques by returning the row number if it's a unique or a formula
blank ("") if it's a dupe.

All rows that are identified as unique on sheet2 I would like to have
that ENTIRE ROW copied to another sheet starting in A2. Row 1 in the
new sheet are the headers.

I can do this easily with formulas but this would require way too many.

So, the basic logic is:

If any cells in column AE on Sheet2 contain a numeric value then copy
that ENTIRE ROW to Sheet3 starting in cell A2 on down.

Thanks!

Biff

Copy entire row if....
 

Hi Rowan!

I tried your code and it also works. Thanks!

Biff

"Rowan Drummond" wrote in message
...
Hi Biff

Try:

Sub test()
Dim eRow As Long
Dim i As Long
Dim CpyRow As Long

CpyRow = 2
With Sheets("Sheet2")
eRow = .Cells(Rows.Count, "AE").End(xlUp).Row
For i = 2 To eRow 'starts in row 2 assuming headings in row 1
If .Cells(i, "AE").Value < "" Then
.Rows(i).Copy Sheets("Sheet3").Cells(CpyRow, 1)
CpyRow = CpyRow + 1
End If
Next i
End With
End Sub

Hope this helps
Rowan

Biff wrote:
Hi Folks!

I've searched the archives and have found a million similar posts but I
don't know enough VBA to adapt what I found to meet my needs.

Comparing uneven ranges on 2 different sheets (Sheet1, Sheet2) looking
for uniques. I'm using a helper column (AE) in Sheet2 that identifies the
uniques by returning the row number if it's a unique or a formula blank
("") if it's a dupe.

All rows that are identified as unique on sheet2 I would like to have
that ENTIRE ROW copied to another sheet starting in A2. Row 1 in the new
sheet are the headers.

I can do this easily with formulas but this would require way too many.

So, the basic logic is:

If any cells in column AE on Sheet2 contain a numeric value then copy
that ENTIRE ROW to Sheet3 starting in cell A2 on down.

Thanks!

Biff

Copy entire row if....
 

You're welcome but as usual Norman's code is slicker <g.

Regards
Rowan

Biff wrote:
Hi Rowan!

I tried your code and it also works. Thanks!

Biff

"Rowan Drummond" wrote in message
...

Hi Biff

Try:

Sub test()
Dim eRow As Long
Dim i As Long
Dim CpyRow As Long

CpyRow = 2
With Sheets("Sheet2")
eRow = .Cells(Rows.Count, "AE").End(xlUp).Row
For i = 2 To eRow 'starts in row 2 assuming headings in row 1
If .Cells(i, "AE").Value < "" Then
.Rows(i).Copy Sheets("Sheet3").Cells(CpyRow, 1)
CpyRow = CpyRow + 1
End If
Next i
End With
End Sub

Hope this helps
Rowan

Biff wrote:

Hi Folks!

I've searched the archives and have found a million similar posts but I
don't know enough VBA to adapt what I found to meet my needs.

Comparing uneven ranges on 2 different sheets (Sheet1, Sheet2) looking
for uniques. I'm using a helper column (AE) in Sheet2 that identifies the
uniques by returning the row number if it's a unique or a formula blank
("") if it's a dupe.

All rows that are identified as unique on sheet2 I would like to have
that ENTIRE ROW copied to another sheet starting in A2. Row 1 in the new
sheet are the headers.

I can do this easily with formulas but this would require way too many.

So, the basic logic is:

If any cells in column AE on Sheet2 contain a numeric value then copy
that ENTIRE ROW to Sheet3 starting in cell A2 on down.

Thanks!

Biff