Based on other criteria, from two cells, I would like to move entire rows
from one sheet to another. There will be more than 7 so I'm thinking I would
need to use a VLOOKUP as opposed to an IF.

Something like:
IF(AND(C1=1,AE5=1),MOVE ROW 5 TO SHEET5!ROW20.

Is this possible? If it is can you move multiple adjacent rows?

thanks for any help!

try

Sub MoveRows()
If Range("c1") = 1 And Range("ae5") = 1 Then
Rows(5).Cut
Sheets("Sheet5").Select
ActiveSheet.Rows("20:20").Select
ActiveSheet.Paste
End If
End Sub


--
mudraker
------------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread...hreadid=537071

Is the only was to accomplish this by way of a macro?

"mudraker" wrote:


try

Sub MoveRows()
If Range("c1") = 1 And Range("ae5") = 1 Then
Rows(5).Cut
Sheets("Sheet5").Select
ActiveSheet.Rows("20:20").Select
ActiveSheet.Paste
End If
End Sub


--
mudraker
------------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread...hreadid=537071

you can not move rows, cells etc by using only formulas


--
mudraker
------------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread...hreadid=537071

I don't have much luck or experience with macros, so I was hoping to be able
to use formulas. Six months ago I didn't have any experience with formulas,
and now except for the occasional problem like this I'm ok. So I'll give it a
whurl. As I tried your suggestion I notices that I really need to match three
criteria. Specifically one from sheet1!(g3) and two from sheet5!(c2:c81) and
(ae2:ae81). Attempting to add the third criteria, and/or possibly the arrays,
resulted in some type of error. Still possible? Any suggestions?

Sorry to drag this out!

Thanks for your help.

"mudraker" wrote:


you can not move rows, cells etc by using only formulas


--
mudraker
------------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread...hreadid=537071

it is still possible

just reply with exact details of what you need to acheive and under
what conditions. If you need to get multiple results then list each one
clearly as seperate steps

Include workbook names, Sheet names, cell address eg a1, Row numbers,
column numbers or letters, ranges of cells/rows etc.

The way you laid it out in you original post is ok even


--
mudraker
------------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread...hreadid=537071

What I would like the macro to do, is sort through and locate, copy and paste
the one correct row based on the criteria in the other two cells. Initially:
Sheet1!G3=1, the macro then needs to search column sheet5! column "C" to
find all that are also "=1" (there could be several) from there it's narrowed
down to the row that column AE = "1" this will give the single row needed to
be copied and pasted into sheet1!, row 19.

Sheet1!G3 could = from 1-13, and the macro would need to search for those
other numbers as well.

Thanks for any direction!

"mudraker" wrote:


it is still possible

just reply with exact details of what you need to acheive and under
what conditions. If you need to get multiple results then list each one
clearly as seperate steps

Include workbook names, Sheet names, cell address eg a1, Row numbers,
column numbers or letters, ranges of cells/rows etc.

The way you laid it out in you original post is ok even


--
mudraker
------------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread...hreadid=537071

Here are 2 macros

The 1st one checks each cell sheet5 column c one cell at a time against
value in sheet3 g3 if match then checks value same row in column ae.
This could be slow if you have a lot of used rows in sheet 5

the 2nd macro uses AutoFilter and you would need to turn auto filter on
in sheet 5.


Sub CopyRow()
Dim wS1 As Worksheet
Dim wS5 As Worksheet
Dim lRow As Long
Dim iValC As Integer
Dim iValAE As Integer
Dim Rng As Range

Set wS1 = Sheets("Sheet1")
Set wS5 = Sheets("sheet5")

iValC = wS1.Range("g3").Value
iValAE = wS1.Range("ae5").Value

For Each Rng In wS5.Range("c2:c" _
& wS5.Cells(Rows.Count, _
"a").End(xlUp).Row)
If Rng.Value = iValC Then
If Cells(0, "ae").Value = iValAE Then
wS5.Rows(Rng.Row).Copy wS1.Rows(19)
Exit For
End If
End If
Next Rng
End Sub


Sub CopyFilterData()
Dim wS1 As Worksheet
Dim wS5 As Worksheet
Dim Rng As Range

Set wS1 = Sheets("Sheet1")
Set wS5 = Sheets("sheet5")

wS5.AutoFilterMode = False
wS5.Activate
Selection.AutoFilter


Selection.AutoFilter Field:=3, Criteria1:=wS1.Range("g3").Value
Selection.AutoFilter Field:=31, Criteria1:=wS1.Range("ae5").Value

With wS5.AutoFilter.Range
On Error Resume Next
' This example will not copy the header row
Set Rng = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not Rng Is Nothing Then
'Copy the cells
Rng.Copy wS1.Rows(19)
End If
End With
End Sub


--
mudraker
------------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread...hreadid=537071

Thanks Mudraker.... thats just the ticket!

"mudraker" wrote:


Here are 2 macros

The 1st one checks each cell sheet5 column c one cell at a time against
value in sheet3 g3 if match then checks value same row in column ae.
This could be slow if you have a lot of used rows in sheet 5

the 2nd macro uses AutoFilter and you would need to turn auto filter on
in sheet 5.


Sub CopyRow()
Dim wS1 As Worksheet
Dim wS5 As Worksheet
Dim lRow As Long
Dim iValC As Integer
Dim iValAE As Integer
Dim Rng As Range

Set wS1 = Sheets("Sheet1")
Set wS5 = Sheets("sheet5")

iValC = wS1.Range("g3").Value
iValAE = wS1.Range("ae5").Value

For Each Rng In wS5.Range("c2:c" _
& wS5.Cells(Rows.Count, _
"a").End(xlUp).Row)
If Rng.Value = iValC Then
If Cells(0, "ae").Value = iValAE Then
wS5.Rows(Rng.Row).Copy wS1.Rows(19)
Exit For
End If
End If
Next Rng
End Sub


Sub CopyFilterData()
Dim wS1 As Worksheet
Dim wS5 As Worksheet
Dim Rng As Range

Set wS1 = Sheets("Sheet1")
Set wS5 = Sheets("sheet5")

wS5.AutoFilterMode = False
wS5.Activate
Selection.AutoFilter


Selection.AutoFilter Field:=3, Criteria1:=wS1.Range("g3").Value
Selection.AutoFilter Field:=31, Criteria1:=wS1.Range("ae5").Value

With wS5.AutoFilter.Range
On Error Resume Next
' This example will not copy the header row
Set Rng = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not Rng Is Nothing Then
'Copy the cells
Rng.Copy wS1.Rows(19)
End If
End With
End Sub


--
mudraker
------------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread...hreadid=537071

glad to be able to help


--
mudraker
------------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread...hreadid=537071