Forum Discussion
kdliske
Mar 04, 2025Copper Contributor
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 macro stops working. Any suggestions? Code is below.
Sub Button3_Click()
'Updated by Kutools for Excel 2017/8/28
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
xRg(K).EntireRow.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
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.
- kdliskeCopper Contributor
Hi, I keep replying but then don't see my reply after, so apologies if this suddenly appears 3x.
This worked perfectly, but opened up a new problem. I am re-using an old workbook and tweaking to update for a new use. I have deleted all of the rows and cleared them, but when I click 'Completed' it is still moving them to the end of the previous table (ex. row 2557) rather than re-starting (ex. row 3). I have found bits of code and suggestions to re-set this, but none of them have worked. Any suggestions? Thank-you again.
This forum doesn't work well, I do see 3 replies now. What if you replace the lines
J = Worksheets("Completed").UsedRange.Rows.Count
If J = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Completed").UsedRange) = 0 Then J = 0
End Ifwith
J = Worksheets("Completed").Range("K1000000").End(xlUp).Row
where K is the column with the status.
- kdliskeCopper Contributor
Thank-you.
Now my issue seems to be that since I am re-using an old spreadsheet (but tweaking it for a new use), the range is stuck as that of the old spreadsheet (ex. it adds the 'Completed' row to row 2557 rather than re-starting at row 3). I have been trying bits of code I am finding online but no dice on re-setting this. Any suggestions?
- ccaabCopper Contributor
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 LongCommandButton3_Click = "Completed"
I = Worksheets("Tasks").UsedRange.Rows.Count
J = Worksheets("Completed").UsedRange.Rows.CountIf J = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Completed").UsedRange) = 0 Then J = 0
End IfSet xRg = Worksheets("Tasks").Range("I3:I" & I)
On Error Resume Next
Application.ScreenUpdating = FalseFor 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
NextApplication.ScreenUpdating = True
End Sub
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.
- kdliskeCopper Contributor
Thank-you, that worked perfectly. Now I'm running into another issue. I am trying to re-use an old spreadsheet for a new purpose and the code is remembering the old table range (ex. so rather than re-starting by moving the 'Completed' row to the first empty line (row 3), it is moving it to row 2574). How would I re-set that?