Moving row to another sheet, with pasting starting in Column B

[cornishpasty55]

Hello,

I would like to move rows from one sheet to another, depending on the content in Column Z. The value for go-ahead is "0" (as this means there are 0 blank cells in the row).

I have managed to move my completed rows from "Complete" to the next available row in "Archive". This pasting into "Archive" starts in Column A. However, I would like the data to start pasting in Column B of Sheet 2, thus leaving Column A empty. I'm thinking I'll need the use of the Offset function, but I'm not sure how to get this working.

Any help is greatly appreciated!

Sub Submit_Request()

    Dim xRg As Range
    Dim xCell As Range
    Dim I As Long
    Dim J As Long
    Dim K As Long
    I = Worksheets("Complete").UsedRange.Rows.Count
    J = Worksheets("Archive").UsedRange.Rows.Count
    If J = 1 Then
        If Application.WorksheetFunction.CountA(Worksheets("Archive").UsedRange) = 0 Then J = 0
    End If
    Set xRg = Worksheets("Complete").Range("Z3:Z" & I)
    On Error Resume Next
    Application.ScreenUpdating = False
    For K = 1 To xRg.Count
        If CStr(xRg(K).Value) = "0" Then
            xRg(K).EntireRow.Copy Destination:=Worksheets("Archive").Range("A" & J + 1)
            xRg(K).EntireRow.Delete
            If CStr(xRg(K).Value) = "0" Then
                K = K - 1
            End If
            J = J + 1
        End If
    Next
    Application.ScreenUpdating = True
      
End Sub

 

[Norie]

All you should need to do is change 'A' to 'B' here.

            xRg(K).EntireRow.Copy Destination:=Worksheets("Archive").Range("A" & J + 1)
[/code[]

 

[cornishpasty55]

Thank you for your suggestion.

Unfortunately, if I replace the 'A' with 'B' nothing appears in my Archive sheet. The row is removed from the Completed sheet, but does not appear at all in my Archive sheet.

 

[Norie]

Didn't notice you were copying the entire row.

Try this, change the 26 to reflect the no of columns you want to copy.

Sub Submit_Request()

Dim xRg As Range
Dim xCell As Range
Dim I As Long
Dim J As Long
Dim K As Long

    I = Worksheets("Complete").UsedRange.Rows.Count
    J = Worksheets("Archive").UsedRange.Rows.Count
    
    If J = 1 Then
        If Application.WorksheetFunction.CountA(Worksheets("Archive").UsedRange) = 0 Then J = 0
    End If

    Set xRg = Worksheets("Complete").Range("Z3:Z" & I)

    Application.ScreenUpdating = False

    For K = xRg.Count To 1 Step -1
        If CStr(xRg(K).Value) = "0" Then
            xRg(K).EntireRow.Resize(, 26).Copy Destination:=Worksheets("Archive").Range("B" & J + 1)
            xRg(K).EntireRow.Delete
            J = J + 1
        End If
    Next

    Application.ScreenUpdating = True

 

[cornishpasty55]

Thank you very much, that works perfectly!