How to copy rows in new row in another worksheet

[Laybenet]

Hi I am new to excel and this forum. I hope my question is simple and one of you can make my weekend :D

I have a workbook with three sheets:
Sheet 1 - Request
Sheet 2 - In Progress
Sheet 3 - Processed

I created Macro that based on conditions met on Sheet1 it copies it to Sheet2 BUT the macro range is set to what I filtered to: Rows("5:21").Select

Here is the code:
Sub Macro1()
' Macro1 Macro
Range("B4").Select
ActiveWindow.ScrollColumn = 2
Range("B4:S4").Select
Selection.AutoFilter
ActiveSheet.Range("$B$4:$S$5").AutoFilter Field:=1, Criteria1:="=", _
Operator:=xlAnd
Sheets("Request Form").Select
Range("B4:S4").Select
Selection.AutoFilter
ActiveSheet.Range("$B$4:$S$109").AutoFilter Field:=18, Criteria1:= _
"APPROVED"
Rows("5:21").Select
Selection.Copy
Sheets("Approved To Be Proccessed").Select
ActiveWindow.ScrollColumn = 1
Range("A6").Select
ActiveSheet.Paste
Rows("4:4").Select
Application.CutCopyMode = False
Selection.AutoFilter
Sheets("Request Form").Select
Rows("4:4").Select
Selection.AutoFilter
Sheets("Approved To Be Proccessed").Select
End Sub

My ultimate goal is to copy approved rows from Sheet 1 copy them to Sheet2 then delete the rows from Sheet1. Then on Sheet 2, create another condition where once it is processed to then deleted it from Sheet2 and copy it to Sheet3. Sheet 3 will be where all completed rows will be held.

Thank you in advance for your help....

 

[asking]

Before you start make a duplicate of your workbook to trial on.

Your sheets must have these exact names
Sheet 1 - Request
Sheet 2 - In Progress
Sheet 3 - Processed
On the sheet Request, select A4:S4, go to the name box and type Req, then press enter.
Now go to the sheet In Progress, select A4:S4, go to the name box and type Prog, then press enter.
Then try this Macro ~

Sub Transfers()
Application.ScreenUpdating = False
Sheets("Request").Select
Range("Req").AutoFilter Field:=18, Criteria1:="Approved"
    Range("Z1").Select
    ActiveCell.FormulaR1C1 = "=SUBTOTAL(2,R[4]C[-25]:R[9999]C[-25])"
    If Range("Z1") = 0 Then
 Selection.AutoFilter
Range("A5").Select
Else
Range("=OFFSET(Request!$A$4,1,0,COUNT(OFFSET(Request!$A$4,1,0,9999)),19)").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("In Progress").Select
    Range("A65536").End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues
 Sheets("Request").Select
    If Range("Z1") > 0 Then
Range("=OFFSET(Request!$A$4,1,0,COUNT(OFFSET(Request!$A$4,1,0,9999)),19)").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Rows.Delete
End If
Selection.AutoFilter
Range("A5").Select
End If

[Color=Red]'This section you need to input your criteria for shifting to sheet Processed.[/Color]
[Color=Red]'Change the AutoFilter field if criteria is in different column to column 'R' (18)[/Color]
Sheets("In Progress").Select
[Color=Blue]Range("Prog").AutoFilter Field:=18, Criteria1:="Put Here your criteria"[/Color]
Range("Z1").Select
    ActiveCell.FormulaR1C1 = "=SUBTOTAL(2,R[4]C[-25]:R[9999]C[-25])"
    If Range("Z1") = 0 Then
Selection.AutoFilter
Range("A5").Select
Else
Range("=OFFSET('In Progress'!$A$4,1,0,COUNT(OFFSET('In Progress'!$A$4,1,0,9999)),19)").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Processed").Select
    Range("A65536").End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Range("A5").Select
 Sheets("In Progress").Select
 Range("Z1").Select
    ActiveCell.FormulaR1C1 = "=SUBTOTAL(2,R[4]C[-25]:R[9999]C[-25])"
    If Range("Z1") > 0 Then
Range("=OFFSET('In Progress'!$A$4,1,0,COUNT(OFFSET('In Progress'!$A$4,1,0,9999)),19)").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Rows.Delete
End If
End If
    ActiveSheet.AutoFilterMode = False
Range("A5").Select
Sheets("Request").Activate
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

When running the macro it will come up with an alert (Twice) " Do you wish to delete rows" click ok

Hope this is what you require. Cheers.

 

[Laybenet]

Thanks for replying. I did as instructed but it is not doing anything. I think the issue is with changing the box name. I was
I was able to only change A4 cell. Am I expected to rename the entire row from A4:S4? I highlighted those rows and changed the box name and it only took effect on A4. Please advice. As usual, I thank all of you guys for any and every help...

 

[asking]

Hi Laybenet, Lets see if we can fix your problem.

Select Cell A1, then look at the name box, which is directly above the cell you just selected it should say A1. Now in that box there is a little arrow, click on that, if you entered the names Req and Prog they should show in the dropdown. If you are in sheet Request click on Req in the dropdown and the cells A4:S4 should high-lite. If A4 to S4 does high-lite (become selected) you entered the names correctly.

If they don't you will have to go to formulas menu, name manager, and select Req, select edit, then in the refers to box type =A4:S4
Do the same while in sheet "In Progress" for the reference Prog =A4:S4

I have fully tested it here but if it still will not work for you, post at least 3 rows of data and the header row. Also let me know what column your criteria is in for filtering to sheet Processed and what the criteria to filter will be and I will modify the macro for you.

It may be that your column A is text, then this formula in the code needs changing ~
"=SUBTOTAL(2,R[4]C[-25]:R[9999]C[-25])"
Change the red 2 and make it a 3.

Cheers

 

[Laybenet]

Hey,

Okay, now the error is at:

Range("=OFFSET(Request!$A$4,1,0,COUNT(OFFSET(Request!$A$4,1,0,9999)),19)").Select


Run-time error '1004'
Method 'Range' of object '_Global" failed.

Is it something I am doing wrong?

Thanks

 

[asking]

Ok, Think I have found the answer.

Your data in column A must be text, to resolve, I have recoded the macro so you don't
miss all corrections. The error in that line was COUNT(OFFSET which I have changed to
COUNTA(OFFSET ~ COUNT only counts if range is values where COUNTA counts Text.

Delete old macro and copy, paste this revised fix.

Sub Transfers()
 Application.ScreenUpdating = False
Sheets("Request").Select
Range("Req").AutoFilter Field:=18, Criteria1:="Approved"
    Range("Z1").Select
    ActiveCell.FormulaR1C1 = "=SUBTOTAL(3,R[4]C[-25]:R[9999]C[-25])"
    If Range("Z1") = 0 Then
 Selection.AutoFilter
Range("A5").Select
Else
Range("=OFFSET(Request!$A$4,1,0,COUNTA(OFFSET(Request!$A$4,1,0,9999)),19)").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("In Progress").Select
    Range("A65536").End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues
 Sheets("Request").Select
    If Range("Z1") > 0 Then
Range("=OFFSET(Request!$A$4,1,0,COUNTA(OFFSET(Request!$A$4,1,0,9999)),19)").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Rows.Delete
End If
Selection.AutoFilter
Range("A5").Select
End If

'This section you need to input your criteria for shifting to sheet Processed.
'Change the AutoFilter field if criteria is in different column to column 'R' (18)
Sheets("In Progress").Select
Range("Prog").AutoFilter Field:=18, Criteria1:="Put Here your criteria"
Range("Z1").Select
    ActiveCell.FormulaR1C1 = "=SUBTOTAL(3,R[4]C[-25]:R[9999]C[-25])"
    If Range("Z1") = 0 Then
Selection.AutoFilter
Range("A5").Select
Else
Range("=OFFSET('In Progress'!$A$4,1,0,COUNTA(OFFSET('In Progress'!$A$4,1,0,9999)),19)").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Processed").Select
    Range("A65536").End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Range("A5").Select
 Sheets("In Progress").Select
 Range("Z1").Select
    ActiveCell.FormulaR1C1 = "=SUBTOTAL(3,R[4]C[-25]:R[9999]C[-25])"
    If Range("Z1") > 0 Then
Range("=OFFSET('In Progress'!$A$4,1,0,COUNTA(OFFSET('In Progress'!$A$4,1,0,9999)),19)").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Rows.Delete
End If
End If
    ActiveSheet.AutoFilterMode = False
Range("A5").Select
Sheets("Request").Activate
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Cheers, Bit of luck should work this time.

 

[Laybenet]

Asking,

It had nothing to do with luck, it had everything to do with your skills! Thank you very much, it WORKED....it WORKED....it WORKED! You my friend is a life saver!

Is there a way to trigger the Macro automatically when the excel is saved?

Laybenet

 

[asking]

Always save a copy to practice on so that you have an original to go back to in case you or the person entering the code stuffs up.

Yes, Try this ~
Do this ~
1. From the workbook you run the macro from, open the macro that you wish to run before closing.

2. Above the first line of code ~ Sub Transfers() there is a box that says (General) click on that, Click workbook.

3. On the right of that box is another box saying Open, click that box and scroll to the top of the box and about half way down you will find a line that says BeforeClose, click that.

You should now see ~
Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub

4. In the blank space between Private.... and End Sub. Type ~
Transfers

Should now look like this ~

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Transfers
End Sub

5. Now close that workbook and re-open to see if it did its job.
Cheers.

 

[asking]

I re-read your post, and see I did not read it properly.
You said run macro when saving not when closing so change line ~

3. On the right of that box is another box saying Open, click that box and scroll to the top of the box and about half way down you will find a line that says BeforeClose, click that.

To ~
3. On the right of that box is another box saying Open, click that box and scroll to the top of the box and about half way down you will find a line that says BeforeSave, click that.

Now it should work when you save the workbook. Now there will be no need to close workbook, just click save to see if it worked

 

[Laybenet]

Asking,

Thank you very much. I am learning so much more about Excel that I ever expected.

I have a cell with this formula in C7: =IF(B7<>"",NOW(),"")
B7 is a field where the person would put their name. I use the formula for time stamp purpose. But I realized that if I open the excel on a different day, it also updates the date for a row/date entered on a different day.

Aside doing the formula on a different cell then copying it to the C7, do you have any recommendation.

I just want to date/time stamp the row that was created.

BTW, when the MACRO copies it to the other sheets, it does copying it as a value not as a formula so that part is perfect.

Thank you very much again!!!!!!!!

Laybenet