If then Function Problems

[MJ]

I have a spreadsheet that has the date in column A in this form "Friday,
March 06, 2008" and I wanted to be able to pull every row that has a friday
and put into a seperate worksheet.

So ever day that is a friday will put then next 4 cells in the row into a
sheet 2.

I know there is a way to do this but my VB is weak to say the least.

Thanks
MJ

 

[Mike H]

Hi,

Alt +F11 to open Vb editor. Right click 'this workbook' insert module and
paste this in

Sub copyit()
Dim MyRange, MyRange1 As Range
Sheets("Sheet1").Select
lastrow = Sheets("Sheet1").Range("A65536").End(xlUp).Row
Set MyRange = Range("A1:A" & lastrow)
For Each c In MyRange
If Weekday(c.Value) = vbFriday Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.Resize(, 5)
MyRange1.Select
Else
Set MyRange1 = Union(MyRange1, c.Resize(, 5))
End If
End If
Next
MyRange1.Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
End Sub


Does that help?
Mike

 

[Mike H]

MJ,

the first instance of the line
myrange1.select
was for testing my code, you can delete it but you need to keep the second
instance

Mike

 

[MJ]

I could not get this to function correctly...I think this is what I am
looking for, but let me re-explain.

A B C D
Friday, ETC 100 -30 Negative
Thursday, ETC 150 20 False

and this goes on and on for all different days.

Now what I need is a script that will choose only the Friday Rows and copy
them to Sheet 2

This is the exact form of my sheet I just don't get how to do it.

Thanks for any and all help

 

[Mike H]

MJ,

The macro reads down column A looking for properly formatted dates that are
a Friday and builds up those rows into a range which it then copies to sheet
2. You may have to change the sheet names to match your requirements.
When I run it it copies like this to sheet 2 bit it will copy what is there
so that doesn't matter, the only thing that does is that the date must
actually be a date.

Friday Mar 07 2008 1 2 3 6
Friday Mar 07 2008 1 2 3 99
Friday Mar 07 2008 5 6 7 99
Friday Mar 07 2008 6 7 8 88

You need to be a bit more descriptive about what not working means but I am
already suspicious of your dates, check they are correctly formatted dates
and not just text.

Mike

 

[MJ]

The dates were originally entered in this format 3/7/08 and then I used the
format tool to change it to look like this Friday, March 7, 2008.

I tired the following script as well

Sub CopyFriday()
'Copy cells of cols A,B,C,D from rows containing "Friday,*" in
'col A of the active worksheet (source sheet) to cols
'A,B,C,D of Sheet2 (destination sheet)
Dim DestSheet As Worksheet
Set DestSheet = Worksheets("Sheet2")

Dim sRow As Long 'row index on source worksheet
Dim dRow As Long 'row index on destination worksheet
Dim sCount As Long
sCount = 0
ARow = 1

For sRow = 1 To Range("A65536").End(xlUp).Row
'use pattern matching to find "Friday,*" anywhere in cell
If Cells(sRow, "A") Like "*Friday,*" Then
sCount = sCount + 1
dRow = dRow + 1
'copy cols A,B,C & D
Cells(sRow, "A").Copy Destination:=DestSheet.Cells(dRow, "A")
Cells(sRow, "B").Copy Destination:=DestSheet.Cells(dRow, "B")
Cells(sRow, "C").Copy Destination:=DestSheet.Cells(dRow, "C")
Cells(sRow, "D").Copy Destination:=DestSheet.Cells(dRow, "D")
End If
Next sRow

MsgBox sCount & " Friday rows copied", vbInformation, "Transfer Done"

End Sub

and this fails as well.

I will try your original script again.

Thanks

MJ