Forum Discussion
Solved
Need help tweaking VBA code so I can add more columns to spreadsheet
Hi, I have a macro with code that allows me to press a button that then moves rows with a certain status to another worksheet. I need to add two more columns to the worksheets, but when I do the macr...
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
