Forum Discussion
Need help tweaking VBA code so I can add more columns to spreadsheet
The code looks for the value "Completed" in column I.
If you insert one or more columns to the left of column I, the status will end up in another column, so you'll have to adjust the code. For example, if status is now in column K, change the line
Set xRg = Worksheets("Tasks").Range("I3:I" & I)
to
Set xRg = Worksheets("Tasks").Range("K3:K" & I)
If that is not correct, please provide more detailed information.
Try to use below code for your macro
Sub Button3_Click()
Dim xRg As Range
Dim xCell As Range
Dim I As Long
Dim J As Long
Dim K As Long
CommandButton3_Click = "Completed"
I = Worksheets("Tasks").UsedRange.Rows.Count
J = Worksheets("Completed").UsedRange.Rows.Count
If J = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Completed").UsedRange) = 0 Then J = 0
End If
Set xRg = Worksheets("Tasks").Range("I3:I" & I)
On Error Resume Next
Application.ScreenUpdating = False
For K = 1 To xRg.Count
If CStr(xRg(K).Value) = "Complete" Then
' Copy the entire row along with two additional columns to the destination sheet
xRg(K).EntireRow.Resize(1, xRg(K).EntireRow.Columns.Count + 2).Copy Destination:=Worksheets("Completed").Range("A" & J + 1)
xRg(K).EntireRow.Delete
If CStr(xRg(K).Value) = "Complete" Then
K = K - 1
End If
J = J + 1
End If
Next
Application.ScreenUpdating = True
End Sub