software

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(quot;c1quot;) = 1 And Range(quot;ae5quot;) = 1 Then
Rows(5).Cut
Sheets(quot;Sheet5quot;).Select
ActiveSheet.Rows(quot;20:20quot;).Select
ActiveSheet.Paste
End If
End Sub--
mudraker
------------------------------------------------------------------------
mudraker's Profile: www.excelforum.com/member.php...foamp;userid=2473
View this thread: www.excelforum.com/showthread...hreadid=537071Is the only was to accomplish this by way of a macro?

quot;mudrakerquot; wrote:

gt;
gt; try
gt;
gt; Sub MoveRows()
gt; If Range(quot;c1quot;) = 1 And Range(quot;ae5quot;) = 1 Then
gt; Rows(5).Cut
gt; Sheets(quot;Sheet5quot;).Select
gt; ActiveSheet.Rows(quot;20:20quot;).Select
gt; ActiveSheet.Paste
gt; End If
gt; End Sub
gt;
gt;
gt; --
gt; mudraker
gt; ------------------------------------------------------------------------
gt; mudraker's Profile: www.excelforum.com/member.php...foamp;userid=2473
gt; View this thread: www.excelforum.com/showthread...hreadid=537071
gt;
gt;


you can not move rows, cells etc by using only formulas--
mudraker
------------------------------------------------------------------------
mudraker's Profile: www.excelforum.com/member.php...foamp;userid=2473
View this thread: www.excelforum.com/showthread...hreadid=537071I 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.

quot;mudrakerquot; wrote:

gt;
gt; you can not move rows, cells etc by using only formulas
gt;
gt;
gt; --
gt; mudraker
gt; ------------------------------------------------------------------------
gt; mudraker's Profile: www.excelforum.com/member.php...foamp;userid=2473
gt; View this thread: www.excelforum.com/showthread...hreadid=537071
gt;
gt;


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: www.excelforum.com/member.php...foamp;userid=2473
View this thread: www.excelforum.com/showthread...hreadid=537071What 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 quot;Cquot; to
find all that are also quot;=1quot; (there could be several) from there it's narrowed
down to the row that column AE = quot;1quot; 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!

quot;mudrakerquot; wrote:

gt;
gt; it is still possible
gt;
gt; just reply with exact details of what you need to acheive and under
gt; what conditions. If you need to get multiple results then list each one
gt; clearly as seperate steps
gt;
gt; Include workbook names, Sheet names, cell address eg a1, Row numbers,
gt; column numbers or letters, ranges of cells/rows etc.
gt;
gt; The way you laid it out in you original post is ok even
gt;
gt;
gt; --
gt; mudraker
gt; ------------------------------------------------------------------------
gt; mudraker's Profile: www.excelforum.com/member.php...foamp;userid=2473
gt; View this thread: www.excelforum.com/showthread...hreadid=537071
gt;
gt;


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(quot;Sheet1quot;)
Set wS5 = Sheets(quot;sheet5quot;)

iValC = wS1.Range(quot;g3quot;).Value
iValAE = wS1.Range(quot;ae5quot;).Value

For Each Rng In wS5.Range(quot;c2:cquot; _
amp; wS5.Cells(Rows.Count, _
quot;aquot;).End(xlUp).Row)
If Rng.Value = iValC Then
If Cells(0, quot;aequot;).Value = iValAE Then
wS5.Rows(Rng.Row).Copy wS1.Rows(19)
Exit For
End If
End If
Next Rng
End SubSub CopyFilterData()
Dim wS1 As Worksheet
Dim wS5 As Worksheet
Dim Rng As Range

Set wS1 = Sheets(quot;Sheet1quot;)
Set wS5 = Sheets(quot;sheet5quot;)

wS5.AutoFilterMode = False
wS5.Activate
Selection.AutoFilterSelection.AutoFilter Field:=3, Criteria1:=wS1.Range(quot;g3quot;).Value
Selection.AutoFilter Field:=31, Criteria1:=wS1.Range(quot;ae5quot;).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: www.excelforum.com/member.php...foamp;userid=2473
View this thread: www.excelforum.com/showthread...hreadid=537071Thanks Mudraker.... thats just the ticket!

quot;mudrakerquot; wrote:

gt;
gt; Here are 2 macros
gt;
gt; The 1st one checks each cell sheet5 column c one cell at a time against
gt; value in sheet3 g3 if match then checks value same row in column ae.
gt; This could be slow if you have a lot of used rows in sheet 5
gt;
gt; the 2nd macro uses AutoFilter and you would need to turn auto filter on
gt; in sheet 5.
gt;
gt;
gt; Sub CopyRow()
gt; Dim wS1 As Worksheet
gt; Dim wS5 As Worksheet
gt; Dim lRow As Long
gt; Dim iValC As Integer
gt; Dim iValAE As Integer
gt; Dim Rng As Range
gt;
gt; Set wS1 = Sheets(quot;Sheet1quot;)
gt; Set wS5 = Sheets(quot;sheet5quot;)
gt;
gt; iValC = wS1.Range(quot;g3quot;).Value
gt; iValAE = wS1.Range(quot;ae5quot;).Value
gt;
gt; For Each Rng In wS5.Range(quot;c2:cquot; _
gt; amp; wS5.Cells(Rows.Count, _
gt; quot;aquot;).End(xlUp).Row)
gt; If Rng.Value = iValC Then
gt; If Cells(0, quot;aequot;).Value = iValAE Then
gt; wS5.Rows(Rng.Row).Copy wS1.Rows(19)
gt; Exit For
gt; End If
gt; End If
gt; Next Rng
gt; End Sub
gt;
gt;
gt; Sub CopyFilterData()
gt; Dim wS1 As Worksheet
gt; Dim wS5 As Worksheet
gt; Dim Rng As Range
gt;
gt; Set wS1 = Sheets(quot;Sheet1quot;)
gt; Set wS5 = Sheets(quot;sheet5quot;)
gt;
gt; wS5.AutoFilterMode = False
gt; wS5.Activate
gt; Selection.AutoFilter
gt;
gt;
gt; Selection.AutoFilter Field:=3, Criteria1:=wS1.Range(quot;g3quot;).Value
gt; Selection.AutoFilter Field:=31, Criteria1:=wS1.Range(quot;ae5quot;).Value
gt;
gt; With wS5.AutoFilter.Range
gt; On Error Resume Next
gt; ' This example will not copy the header row
gt; Set Rng = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) _
gt; .SpecialCells(xlCellTypeVisible)
gt; On Error GoTo 0
gt; If Not Rng Is Nothing Then
gt; 'Copy the cells
gt; Rng.Copy wS1.Rows(19)
gt; End If
gt; End With
gt; End Sub
gt;
gt;
gt; --
gt; mudraker
gt; ------------------------------------------------------------------------
gt; mudraker's Profile: www.excelforum.com/member.php...foamp;userid=2473
gt; View this thread: www.excelforum.com/showthread...hreadid=537071
gt;
gt;


glad to be able to help--
mudraker
------------------------------------------------------------------------
mudraker's Profile: www.excelforum.com/member.php...foamp;userid=2473
View this thread: www.excelforum.com/showthread...hreadid=537071