Solved: Dynamically determine week number and filter

[zale86]

I have a column which is the Date. I have a cell which displays current week. I would like to create a macro which will filter based on the PREVIOUS week, copy only the cells containing data within the filtered result, ignoring the first three rows, and then paste the data into another workbook. I can handle the paste end but don't know how to do the filter part. I initially had a hidden column with a formula to determine the week of each entry so I could simply filter on that column, but having that formula present in the entire column makes the worksheet unusable. I can't trust the user to copy the formula down as needed either.

I've attached the spreadsheet I'm working with just for reference.

TIA

[p45cal]

This little snippet filters the range for the previous week, though you will want to tweak the start/end of the week a bit, and then copies it to a new sheet. You should be able to finalise something from it:[vba]Sub blah()
stdate = Date - (Date Mod 7) - 5
With Sheets("Sheet1")
.Range("A3").AutoFilter Field:=1, Criteria1:=">=" & CLng(stdate), Operator:=xlAnd, Criteria2:="<" & CLng(stdate + 7)
Set NewSHt = ActiveWorkbook.Sheets.Add(After:=Sheets(Sheets.Count))
.Range("_FilterDatabase").Resize(.Range("_FilterDatabase").Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).Copy NewSHt.Range("A1")
' .Range("_FilterDatabase").Resize(.Range("_FilterDatabase").Rows.Count - 1, 4).Offset(1).SpecialCells(xlCellTypeVisible).Copy NewSHt.Range("A1")'instead of the line above to include the fourth column
End With
End Sub
[/vba]

[zale86]

Your code certainly does what you say. Thanks! What I actually need to do is take that data and stick it into the next unpopulated rows in a different workbook, and add the week number into a column as well as the username. Ultimately I then have everyone's time in one workbook with username and week number for filtering and sorting purposes.
Thanks again

[p45cal]

You said:

I can handle the paste end

but now:

What I actually need to do is take that data and stick it into the next unpopulated rows in a different workbook

[vba]Sub blah()
stdate = Date - (Date Mod 7) - 5
With ThisWorkbook.Sheets("Sheet1")
.Range("A3").AutoFilter Field:=1, Criteria1:=">=" & CLng(stdate), Operator:=xlAnd, Criteria2:="<" & CLng(stdate + 7)
Set SourceRange = .Range("_FilterDatabase").Resize(.Range("_FilterDatabase").Rows.Count - 1, 4).Offset(1).SpecialCells(xlCellTypeVisible)
Set DestRange = Workbooks("another.xls").Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1)
SourceRange.Copy DestRange
DestRange.Offset(, SourceRange.Columns.Count).Resize(SourceRange.Rows.Count) = Application.WorksheetFunction.WeekNum(stdate)
End With
End Sub
[/vba]We're beginning to see goalposts moving and scope-creep with

and add the week number into a column as well as the username

The above code handles week number but what do you mean by username?

I'm wondering, when you first wrote "I would like to create a macro which will…" whether you meant to write "I would like someone else to create a macro which will…"

meeoow!

[zale86]

Sorry man, If I was trying to get someone to write the whole thing I'd have asked for that in the first place. I'm trying to learn and often for me the answer to one question leads to another. Don't mean to come off like a leach.
thank you for your help.

[zale86]

Using what you generously provided I have been able to study it and adapt it to do what I ultimately need it to do. I still prefer to ask the minimum and only ask for more help if I need it.
Thanks again,
D