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

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
"MJ" wrote:

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

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" wrote:

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

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" wrote:

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" wrote:

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

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" wrote:

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" wrote:

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" wrote:

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

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

"Mike H" wrote:

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" wrote:

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" wrote:

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" wrote:

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