Macro

[xxsalahxx]

Hello im looking for a macro that will search through column A in a worksheet that is in a date format "Sunday/12/07/2020"
If any days/dates show Saturday or Sunday id like to copy this data and paste into another sheet.

1st Worksheet tab is named Data (copy from this sheet)
2nd Worksheet tab is named Data2 (paste into this sheet)

I would need to copy over the whole rows of data

Thank you in advance for any help.

 

[Joe4]

Are your values in column A entered as text, so the actual value entered into the cell is like "Sunday/12/07/2020", or are they entered as dates, and then have a custom format applied to make them appear this way?

Approximately how many rows of data do you have?

 

[xxsalahxx]

Are your values in column A entered as text, so the actual value entered into the cell is like "Sunday/12/07/2020", or are they entered as dates, and then have a custom format applied to make them appear this way?

Approximately how many rows of data do you have?

Hello, thank you for your reply
The cells seem to be formatted as a custom dddd/dd/mm/yyyy

Approx rows would be 1000 however could be more as the months go on

 

[Joe4]

OK, there may be more efficient ways than using loops (i.e. using Filters, possibly), but 1000 rows should be pretty quick.

Try this:

Sub MyCopy()

    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim lr As Long, r As Long
    
    Application.ScreenUpdating = False
    
'   Set worksheet variables
    Set ws1 = Sheets("Data")
    Set ws2 = Sheets("Data2")
    
'   Find last row in column A on source sheet with data
    lr = ws1.Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows on source sheet, starting on row 2
    For r = 2 To lr
'       Check to see if date in column A is Saturday or Sunday
        If Weekday(ws1.Cells(r, "A"), vbMonday) >= 6 Then
            ws1.Rows(r).Copy ws2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        End If
    Next r
        
    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!"
    
End Sub

 

[xxsalahxx]

Ok thanks i have tried this but its saying subscription out of range
Any ideas?

 

[Joe4]

Are your sheets really named "Data" and "Data2"?
If not, you will need to update that part of my code to reflect what the real names of your sheets are.

Also, you may need to confirm that the values in column A really are entered as dates. If they are entered as text, changing the format has no effect on them.
If you try changing the format of any date in column to any other date format, does it change?
If it does not, then you are dealing with text entries, not date ones, and we will need to amend the code to reflect that.

 

[xxsalahxx]

Perfect I have done that and its working perfectly, thank you so much for your help with this its much appreciated.

 

[Joe4]

You are welcome.
Glad I was able to help.