Automatically Copy Row to another sheet with specific columns if Date is less than today's date

[hakantoz]

Hello Everyone,

I have been doing some reach in the forum trying to find the answer of my question but haven't quite found the thing that I want so following up on that.

I have two sheets, sheet1 that have multiple projects on Column A and Column L have multiple dates. What I want to happen is, if the date on Column L is less than or equal to today's date, copy that row's specific columns (A,F,G,H,K,L,R,U) in to Sheet2 starting with A3 and add a new row as data is entered. And continue doing this until all projects on sheet1 ( Column A ) is checked one by one and copied to sheet2 with respective columns. I would say, it should not exceed 200 rows overall.

Can this even be done without VBA macro script?

 

[mumps]

Try:

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("L1:L" & LastRow).AutoFilter Field:=1, Criteria1:="<=" & Date
    Intersect(Rows("2:" & LastRow), Range("A:A,F:H,K:L,R:R,U:U").SpecialCells(xlCellTypeVisible)).Copy Sheets("Sheet2").Cells(3, 1)
    Range("L1").AutoFilter
    Application.ScreenUpdating = True
End Sub

 

[KUYJS]

Insert one column at the begining of sheet and name it 0. Use formula in cell A2 as =if(L2<=today(),max($A$1:A1)+1,0)
it will tell you if this row meets your criteria or not. Copy this down till your data points

Now on sheet2 insert one column at the begining with name Sr. No with simple count of 1, 2, 3... Use vlookup from sheet1 to get your required column in sheet2

 

[hakantoz]

Hmm Interesting,

What I have done is, copied this code to the sheet1, then changed the copied sheet name to actual sheet name and once I ran it, I see an error stating, object variable or with block variable is not set. It also hid my first 100 rows...

 

[mumps]

When I tried the macro on some dummy data it worked properly. Place the macro in a standard module and run it from there. Make sure that Sheet1 is the active sheet when you run the macro. When you got the error and clicked "Debug", which line of code was highlighted?

 

[hakantoz]

Intersect(Rows("2:" & LastRow), Range("A:A,F:H,K:L,R:R,U:U").SpecialCells(xlCellTypeVisible)).Copy Sheets("Test").Cells(3, 1) - gets highlighted.

get the same error too.

 

[mumps]

Can you post a screen shot of what your data looks like? Section B at this link has instructions on how to post a screen shot: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html Alternately, you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[hakantoz]

Ok as I was trying to simplify the code I found two things

1) If filter is active in my initial sheet1, code is throwing an error. Once remove all the filter code works. So question is how can we still have the code run through even if I have filters?
2) Another thing I also realized is that, once it starts adding the copied cells in to the second sheet, it just copies without adding a new line where it overwrote some of the things below on sheet2. How can we make sure each copied cell is on a new line?

 

[mumps]

What do you mean when you say:

If filter is active in my initial sheet1

The macro copies multiple rows each time you run it. Are you running the macro more than once? If you run it more than once, do you add or delete any data in between runs?

 

[hakantoz]

So I do have couple filters where one sorts by project type, quarter and date that is sort in ascending on the main page. If those are active, the error I mentioned above shows up.

Also on the macro, I ran it once then deleted the copied data and tested out with one of the rows to see whether information on that row would be pushed down or not. What it did was, it copied the data without creating a new line ( sorry if wasn't too clear about this one before ).