Automatic Switch Between Worksheets

[Swift Salmon]

Hi all,

So I need to create a formula/macro to automatically move a row from one worksheet to the other. The gist of it is that once you change a status from "active" to "closed" (which you manually write in) the row that is now "closed" will move from Worksheet 1 to Worksheet 2. Is this even possible to do? I don't have much experience with macros or changing around the code in the workbook so detail is much appreciated. In reference to the actual information, there will be headers across the top that read "status", "cost", "department" and so on. Any help would be awesome and thank you very much.

 

[vcoolio]

Hello SwiftSalmon,

The following code may get you started:-

Sub MoveIt()

Application.ScreenUpdating = False

Dim lRow As Long

lRow = Range("A" & Rows.Count).End(xlUp).Row

For Each cell In Range("L2:L" & lRow)
If cell = "Closed" Then
cell.EntireRow.Copy
Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
cell.EntireRow.ClearContents
End If
Next

Columns("A").SpecialCells(4).EntireRow.Delete
Application.ScreenUpdating = True
Application.CutCopyMode = False
Sheets("Sheet2").Select

End Sub

I have attached a sample work book for you to play with here:-

https://www.dropbox.com/s/2d8omidxqh4ex8n/SwiftSalmon.xlsm?dl=0

Type "Closed" in any cell in Sheet 1, Column L (named Criteria), as many cells as you like, click on the Transfer Data button and the data will be transferred to Sheet 2.

The code will select an entire row (one or many) with the criterion "Closed" in any cell in Column L, Sheet 1 and copy it to the first available row in Sheet 2 and will copy data as required to each consecutive available row thereafter. The "used" data from Sheet 1 will be cleared.

I don't know how your work book data is set out but I have just randomly selected Column L for the "Closed" criterion. You may have to change the cell and sheet references in the code to suit yourself. "Closed" is case sensitive.

To implement the code in your work book, press Alt + F11. This will open the Visual Basic Editor. Up the top left side, select "Insert" then select "Module". The big grey field will turn white and, in this field, just paste the above code. As I said above, just change the cell and sheet references to suit your work book before running the code.

Good luck with it. Post back if you have any questions.

Cheerio,
vcoolio.

 

[Swift Salmon]

Wow! Awesome!
Two quick questions:
1) In my own workbook how do I get the button to appear?
2) Which cells do I have to change? Is it only where it reads "For Each cell In Range("L2:L" & lRow)" that I need to change to the row that my criteria is in?

Thank you so much!

 

[vcoolio]

Hello SwiftSalmon,

Firstly, it may be worth adding the following little line of code to the macro above:-

Sheets("Sheet1").Select

just after this line of code:-

lRow = Range("A" & Rows.Count).End(xlUp).Row

You'll need to change just the "L" reference to whatever column you have your criteria in. For example, if its in Column M, then change it to "M2:M" & lRow. The "2" refers to the row number where the code starts its search (headings in row 1 are excluded).

To create a button, click on the "Insert" tab at the top of your sheet and select "Shapes" from the Illustrations group. From the drop down that appears, select a shape of your liking (I usually use a rectangle). On selecting a shape, your pointer will turn into cross hairs. With the cross hairs, draw/extend your shape to the size you want where ever you want on your sheet (A "Drawing Tab" will appear with many "Shape Styles" to select from. Go ahead and spoil yourself!). Right click on the shape and in the menu that appears, select "assign macro". A pop-up will appear with the name of the macro in the larger box (in the sample work book it is named MoveIT). Click on the name to high-light it then click OK. Done! Every time you click on the button the code will execute ( as long as the criteria is placed in the relevant cell in the relevant column). Of course you can format the shape to your liking so I suggest that you play with this function in a blank work sheet before you actually apply it to your actual work book.

Also, in the code, where ever you see a reference to "Sheet2", change it to whatever your actual sheet is named but be sure to spell it in the code exactly as it is in the sheet tab including cases, spaces, underscores etc.. The same for Sheet 1.

Have fun!

Cheerio,
vcoolio.