0

I am having some issues getting 2 macros buttons to run. They are very similar to one another but Macro 2 is a bit more complicated and quite honestly... I am new to VBA so I have been learning as I have been going along. I was able to piece the below code together but learned afterward that deleting rows destroys the workbook flow and cant seem to get it working like it needs to.

Macro 1 (example below): If the word 'Move' is typed into Column Q and the button (macro) is hit on the 'Projects' page then excel will erase data out of the specific cells but leave the row intact (it doesn't delete the row, just erases data from cells) and paste into the next blank row on a sheet called 'Pending'. I have included a link below for an example of the 'Projects' page. The data starts on row 5 on the 'Projects' sheet and row 3 on all the others. A, B and E are dropdowns.

Macro 2: I am needing a separate macro that is very similar to the above. The main difference is that you type 'Move' in column R and hit the button it 1. runs the code in macro 1 but copies to the first blank row in a sheet called 'Tracking' instead of 'Pending' 2. it searches all other sheets except sheets 'Projects' and 'Tracking' and deletes the entire row instead of just erasing cells. It still just erases on the 'Projects' page.

For a general understanding, Macro 1 will be used to put files in a 'Pending' status and Macro 2 will be used to 'Close' files and leave their trace in 'Tracking'.

Here is what I came up with for Macro 1 which deletes the rows and is slow/ freezes excel on occasion:

Sub RoundedRectangle4_Click()


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

Projects Screenshot

'Projects' Screenshot

Once again, I really appreciate any help and I would love to learn how to get something like this working in excel for future use if you have any notes.

3
  • Normally when deleting rows you want to work from the bottom up: see here Commented May 1, 2018 at 16:46
  • Sadly, I was not needing to delete rows. How could I replace that with it just erasing date from certain cells? Commented May 2, 2018 at 20:20
  • Sounds like you need the Range.Clear method - see here. Also, I recommend not using On Error Resume Next... Much better to handle your error, rather than just ignore it, which is what On Error Resume Next does. Commented May 2, 2018 at 20:26

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.