How to move up/down multiple rows?

Excel/macro to move rows between worksheets

• You posted the VB script below last December as a solution to conditionally moving rows from ansheet to another. The only problem I have with it offsets the rows by one column to the right when it moves a row. For example, it will take row A3:D3 from one sheet and move it to B3:E3 in the other sheet. Sub move_to_archive() Dim sh_from As Worksheet, sh_to As Worksheet Dim r As Range, col As Long, r1 As Range, la As Long Dim cnt As Long Dim last As Long col = Range("m6").Column 'DONDE ESTA RESOLVED Set sh_to = Worksheets("Notices and Freezes - Archive") Set sh_from = Worksheets("Notices and Freezes - propose") sh_from.Activate Set r = Sh.Columns(col).Find( _ What:="Resolved", _ After:=Sh.Cells(Rows.Count, col), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) If r Is Not Nothing Then Set r1 = sh_to.Cells(Rows.Count, col).End(xlUp)(2) Set r1 = r1.Offset(0, -(col - 1)) r.EntireRow.Copy r1.EntireRow.PasteSpecial xlValues r1.EntireRow.PasteSpecial xlFormats cnt = cnt + 1 r.EntireRow.Delete End If MsgBox cnt & " cases moved to Archive" End Sub Is there something specific in the script which does this or could be added to move the row to A3:D3. Many Gary

• Answer:

  Gary, the way the code is written, it would not be able to do that. Here is an illustration from the immediate window. In the code, col is defined as row M and the variable r1 will reference a cell in column M. So: col = Range("M1").Column ? cells(2,"M").offset(0,-(col - 1)).Address $A$2 so if I use the code the macro uses and I am offsetting from column M, that give me column A. Also, the macro copies an entirerow. It isn't copying just A3:D3. It is copying row 3. So when it pastes, if the destination to column 2/column B, then it would produce an error - you can't paste an entire row unless you have an entirerow to receive it. If you designate a single cell it must be in column A and that is translated byinto an entire row. So I can't explain why you would think it moves the data from column A to column B.