VBA code to Select Week Ending Date

[Datle425]

Hi,

I need help writing a VBA code that will select the whole row with the same weekending date, then copy and pasting it into a different spreadsheet. I've researched everywhere but can't find anything.

Example:

Cost Date Week Ending $1,539.36 2/15/2012 2/17/2012 $35.51 3/19/2012 3/23/2012 $54.01 5/16/2012 5/18/2012 $717.34 5/23/2012 5/25/2012 $124.60 5/23/2012 5/25/2012 $176.01 6/1/2012 6/1/2012 $104.38 6/6/2012 6/8/2012 $647.08 6/11/2012 6/15/2012 $1,108.95 6/14/2012 6/15/2012 $1,202.84 6/14/2012 6/15/2012 $792.10 6/14/2012 6/15/2012 $415.74 6/15/2012 6/15/2012 $1,596.40 6/18/2012 6/22/2012 $1,589.80 6/18/2012 6/22/2012 <colgroup><col><col><col></colgroup><tbody> </tbody>

<colgroup><col><col></colgroup><tbody>
</tbody>

Thanks for the help!

 

[Bairn90]

Hi

Something like this should do you. It's not the most clean code in the world but it'll do the trick.

Dim datecell As Integer
Dim rng As Integer

rng = Application.WorksheetFunction.Count([B:B])

For datecell = 2 To rng + 1

If Range("B" & datecell).Value = Range("C" & datecell).Value Then
Worksheets(1).Rows(datecell).Copy
Windows("Book2").Activate
[A2].Select
ActiveSheet.Paste
End If

Windows("Book1").Activate

Next datecell

If you have any questions let me know.

 

[Datle425]

Hi,

The problem I'm having trouble is selecting ALL the week ending date and pasting it. So it could be 1 row to 100 rows with the same week ending date. I don't know any statement that can do that.

Also your code won't paste. Has an error msg at "Activesheet.paste"

Thanks for the help!
Example:

Cost Date Week Ending $1,539.36 2/15/2012 2/17/2012 $35.51 3/19/2012 3/23/2012 $54.01 5/16/2012 5/18/2012 $717.34 5/23/2012 5/25/2012 $124.60 5/23/2012 5/25/2012 $176.01 6/1/2012 6/1/2012 $104.38 6/6/2012 6/8/2012 $647.08 6/11/2012 6/15/2012 $1,108.95 6/14/2012 6/15/2012 $1,202.84 6/14/2012 6/15/2012 $792.10 6/14/2012 6/15/2012 $415.74 6/15/2012 6/15/2012 $1,596.40 6/18/2012 6/22/2012 $1,589.80 6/18/2012 6/22/2012 <tbody> </tbody>

<tbody>
</tbody>

 

[Bairn90]

Hi,

Apologies that I misinterpreted what you were asking for. I'm still not 100% sure though. Do you want VBA code that will copy any rows where the date in the "Week Ending" column is actually the week ending date for the date given in column B? In the example you've given above every date in the weekend ending column is the weekending date so I'm a little confused as to why certain rows are highlighted red.

In any case I've quickly hashed together a formula that will give the working week ending date for any date given (except weekends)

=B2+5-WEEKDAY(B2,11)

It takes the date + 5 for the number of working days, - the number of the work day. i.e. Tuesday is day 2 so using this week as an example, 04/09/13 + 5 gives 04/14/13 - the 2 for Tuesday gives 04/12/13.

If you put in a weekend date it will still give the Fridays date but that can be covered for if need be.
For example if you were to input the 04/13/13 it would return 04/12/13.

I'm not sure why the code fell over on Activesheet.Paste as that works perfectly fine normally. Do you know what the error message was?

Any questions let me know.

 

[Datle425]

Hi Bairn90, No need to apologies. You are very kind to help me.

This is the data I'm given.

Cost Date $1,539.36 2/15/2012 $35.51 3/19/2012 $54.01 5/16/2012 $717.34 5/23/2012 $124.60 5/23/2012 $176.01 6/1/2012 $104.38 6/6/2012 $647.08 6/11/2012 $1,108.95 6/14/2012 $1,202.84 6/14/2012 $792.10 6/14/2012 $415.74 6/15/2012 $1,596.40 6/18/2012 $1,589.80 6/18/2012 <tbody> </tbody>

<tbody>
</tbody>

I added the "weekending column" using =B2 + 6 - Weekend (B2) to get the weekending date.

Now my job is to break up the data by its week ending date. Meaning (Sheet 1 has the data):

Sheet 2:

Cost	Date	Week Ending
$1,539.36	2/15/2012	2/17/2012

<tbody>
</tbody>

Sheet 3:

$35.51

3/19/2012

3/23/2012

<tbody>
</tbody>

Sheet 4:

$54.01

5/16/2012

5/18/2012

<tbody>
</tbody>

Sheet 5:

$717.34	5/23/2012	5/25/2012
$124.60	5/23/2012	5/25/2012

<tbody>
</tbody>

....Sheet 8:

$647.08	6/11/2012	6/15/2012
$1,108.95	6/14/2012	6/15/2012
$1,202.84	6/14/2012	6/15/2012
$792.10	6/14/2012	6/15/2012
$415.74	6/15/2012	6/15/2012

<tbody>
</tbody>

Notice how Sheet 5 and 8 has more entries with the same weekending date.

That's the problem I'm running into. What VBA code will identify all the weekending date, then copy and paste it to a different sheet.

Also, do you know how to combine week ending date with month ending date?

An example, say Friday is 4/26, so I cut everything out between 4/20 to 4/26. But I also need to cut out between 4/26 to 4/30 as well.

If you want, I can attach an example.

Thanks!

 

[Bairn90]

Sorry for the delay, forum didn't seem to work on Tuesday and the yesterday I was working.

The below should do what you need. It uses the filtering to loop through each new weekending date and then copy and paste them to a new sheet.

Sub datesplit()


Dim lastrow As Integer
Dim rng As Object
Dim newsheet As Worksheet


Sheets("Sheet1").Select


With Sheets("Sheet1")
lastrow = .Cells(Application.Rows.Count, 3).End(xlUp).Row
End With


For Each cell In Range("C2:C" & lastrow)
ActiveSheet.Range("$A$1:$C$" & lastrow).AutoFilter Field:=3, Operator:= _
        xlFilterValues, Criteria1:=Array(1, cell)
countrange = "C2:C" & cell.Row
If Application.WorksheetFunction.CountIf(Range(countrange), cell) < 2 Then
ActiveSheet.AutoFilter.Range.Copy
Set newsheet = Sheets.Add(After:=Sheets(Worksheets.Count))
newsheet.Select
ActiveSheet.Paste
[A:C].Select
[A:C].EntireColumn.AutoFit
[A1].Select
Sheets("Sheet1").Select
End If
Next cell
[A:C].AutoFilter


End Sub

 

[Datle425]

Thank you Bairn90! It did the trick!

Can you also help me with this small part?

I'm using this Date Ending formula to calculate the Week Ending (Friday)

=C2 + 6 - Weekending (C2)

I'll give me:

4/6 to 4/12
4/13 to 4/19
4/20 to 4/26
4/27 to 5/3

Instead of giving me 5/3 because I don't want it to bring it to the next month. Do you know how to make it 4/27 to 4/30? I need it to cut off at the last day of the month.

So basically combing weekending with the month ending date.

 

[Bairn90]

You'd use the =eomonth to get the last day of the month.

Then say =if(eomonth(C2,0)<*InsertYourWeekendingFormulaHere*,eomonth(C2,0),weekending date)

 

[Datle425]

Wow! Thanks.

I was using a different way to get the EOMonth haha.

=IF(MONTH(C2)=MONTH(RC[-1]), """", DATE(YEAR(C2),1+MONTH(C2),0))

That's what I was using before and had to use two columns.

Another question lol sorry. This is the last one.

Sheets("Sheet1").Activate




With Sheets("Sheet1").Activate
lastrow = Sheets("Sheet1").Cells(Application.Rows.Count, 4).End(xlUp).Row
End With

I tried changing it to it uses the active sheet but I'm guessing something is wrong with "last row =" part.

 

[Bairn90]

Hi,

Not sure what the problem is as the code you've supplied works fine. A variation of this using activesheet could be:

lastrow = ActiveSheet.Cells(Application.Rows.Count, 4).End(xlUp).Row