0

I'm working on a project for work and the last piece of the puzzle is creating a macro button on the end of each row. The macro will move the info to a second sheet and clear the info entered on the first sheet.

I need this for 1000 rows, is there a way to create 1000 macros that are only formatted for the row they are attached to?

I created the code below using the record button in Excel, I'm very new to VBA so any advice would be gladly appreciated it!

UPDATED VBA!

Dim Cell As Range
With Sheets(1)
      For Each Cell In .Range("H2:H" & .Cells(.Rows.Count, "H").End(xlUp).Row)
        If Cell.Value = "Completed" Then

            .Rows(Cell.Row).Copy Destination:=Sheets(2).Rows(Cell.Row)
            .Rows(Cell.Row).SpecialCells(xlCellTypeConstants).ClearContents
        End If
    Next Cell
End With

End Sub
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
C. Reed
  • 1
  • 1
  • 1
    Seems like you could benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/4996248). Macro-recorded code isn't very good code and shouldn't be used as a template for anything serious. Also, I don't know exactly what you are doing, but I seriously doubt that creating 1000 buttons and 1000 macros is a good design. Perhaps just 1 button and 1 macro which calls up a userform which allows the user to select the row. – John Coleman Jan 25 '21 at 01:00
  • 4
    I doubt that your user has the capability to click 1000 buttons at the same time, nor is your program likely able to process the clicks. Therefore your actual requirement is for just one button which can be made to appear in different places. As an alternative, consider designating the cells in one column to act like a button when clicked. Instead of cells dedicated to being clicked, I often use existing cells to respond to a double-click, such as the first cell in a row to add a row or the last to carry out some action. – Variatus Jan 25 '21 at 01:13
  • @Variatus Your alternative idea sounds promising. – John Coleman Jan 25 '21 at 01:15
  • The sheet is to track pending customer issues. The user would only be closing out 1 issue at a time but this sheet if for the entire site. This could have up to 1000 issues at a time which is why I was looking for it to have that many. You have a point, this would slow it down to a crawl. I need cells A2:J2 copied and pasted to sheet 2 then the original cells on sheet 1 needs to be replaced with blank cells/drop downs. – C. Reed Jan 25 '21 at 04:11
  • I've been looking up answer and playing with the VBA. I have the button working almost perfectly. The only issue is the macro will copy the entire row instead of the range I need, so it's copying/pasting the button to the second page. The range I'm shooting for is A:J on the row that was copied of course. – C. Reed Jan 25 '21 at 05:18
  • 2
    `.Cells(Cell.Row, 1).Resize(1, 10).Copy Destination:=Sheets(2).Rows(Cell.Row)` – Tim Williams Jan 25 '21 at 05:31

0 Answers0