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]

Hi Laybenet, If you only wish to date stamp and you only use simple formulas in your sheets that are not likely to create circular references you can do this ~

1. Click on the excel orb, click excel options, click formulas, tick the box where it says Enable iterative calculation, click OK. Then copy this formula and paste into C7

=IF(ISBLANK(B7),"",IF(C7="",TODAY(),C7))
This formula can be filled down or up as required and now once a name has been entered into column B the date will not change.

Another way is to write a macro that selects only cells in column C that are not blank (the date is showing) copy those and paste as values.

By utilizing your new knowledge from our last exercise using "BeforeSave", you can now convert your dates to a value date and not a formula date every time you save the workbook.

Cheers.

 

[Laybenet]

Hi Asking,

File I created in Excel 2010 works fine but when I try to recreate it with Excel 2003, I get the following error:

Run Tme Error '1004'
Delete Method of Range Class Failed.

Here is the code:

Sub Transfers()
Application.ScreenUpdating = False
Sheets("Request").Select
Range("Req").AutoFilter Field:=32, 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)),34)").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)),32)").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:=33, Criteria1:="Completed"
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)),34)").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)),34)").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

Any Suggestion?

 

[asking]

Unfortunately I don't have excel 2003 to test on.

The part that seems to be the problem is ~

Sheets("Request").Select
If Range("Z1") > 0 Then
Range("=OFFSET(Request!$A$4,1,0,COUNTA(OFFSET(Request!$A$4,1,0,9999)),32)").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Rows.Delete
End If

In a separate module put this sub below to find out if it is the problem. After entering it, step through to see what happens.
Is Z1 >0?
Is it selecting the range to delete?
When it gets to ~ Selection.SpecialCells(xlCellTypeVisible).Select
does it actually select only the visible cells?

 Sub Test2003()
Sheets("Request").Select
Range("Req").AutoFilter Field:=32, Criteria1:="Approved"
Range("Z1").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(3,R[4]C[-25]:R[9999]C[-25])"
If Range("Z1") > 0 Then
Range("=OFFSET(Request!$A$4,1,0,COUNTA(OFFSET(Request!$A$4,1,0,9999)),32)").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Rows.Delete
End If
End Sub

I finally spotted that you sent me a message LOL. But as I don't have excel 2003 your suggestion would be of no use.

Update me on how you go.
Someone else with excel 2003 might have the answer as to why you are coming up with that error.