Copy data from sheet 1 to sheet 2 based on day/date
 

I have a workbook containing several worksheets, i have a "master"
worksheets called "Day" it has columns A to AR in B column i have a
range of dates for the year

I want to organise the data, i want to do this by copying the data to a
series of worksheets called "Monday", "Tuesday" right through to
"Sunday".

When I update my master worksheet "Day" I want the data to be
copied/updated to the corresponding worksheet e.g. if 10-Oct-05 is a
Monday copy/update the data to the "Monday" worksheet and so on right
through to Sunday.

Any ideas? macro or just general linking of cells?

I have tried everything i can think of!

Any help would be appreciated!

Jon

Copy data from sheet 1 to sheet 2 based on day/date
 

Probably a simple loop down column B to determine which sheet to copy to
would do it. How is the "Day" sheet updated? Is it wiped clean with all new
data, or append new data to the bottom (or top)? If it is wiped clean is old
data included with the new data during the update? The answer will help to
write the code so that duplicate old data will not be copied again the next
time the macro is run.

Mike F
wrote in message
ups.com...
I have a workbook containing several worksheets, i have a "master"
worksheets called "Day" it has columns A to AR in B column i have a
range of dates for the year

I want to organise the data, i want to do this by copying the data to a
series of worksheets called "Monday", "Tuesday" right through to
"Sunday".

When I update my master worksheet "Day" I want the data to be
copied/updated to the corresponding worksheet e.g. if 10-Oct-05 is a
Monday copy/update the data to the "Monday" worksheet and so on right
through to Sunday.

Any ideas? macro or just general linking of cells?

I have tried everything i can think of!

Any help would be appreciated!

Jon

Copy data from sheet 1 to sheet 2 based on day/date
 

thanks for the reply! the master sheet "Day" contains all of my
data/the data is apended to it....then I want the additional 7
worksheets to contain information depending on day e.g. monday, tuesday
etc...the data is naturally copied from the master sheet.

Copy data from sheet 1 to sheet 2 based on day/date
 

Hi

If you have a column in you data table with the day name or number you can use this
http://www.rondebruin.nl/copy5.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl


wrote in message ups.com...
thanks for the reply! the master sheet "Day" contains all of my
data/the data is apended to it....then I want the additional 7
worksheets to contain information depending on day e.g. monday, tuesday
etc...the data is naturally copied from the master sheet.

Copy data from sheet 1 to sheet 2 based on day/date
 

OK. So to keep from copying the same old data each time the macro is run,
would it be OK to mark that row in column AS with a "C" once it has been
copied to another sheet. That way the macro can look for the "C" in column
AS, and if it is not there, evaluate the date in column B, copy the row to
the appropriate sheet, put a "C" at the end of the row, and go to the next
row.
If it is OK to use column AS to mark the row as 'copied', then code can be
written. As an option, any spare column can be used and it can be hidden
from view if the "C" is visually unappealing on the "Day" sheet.

Mike F
wrote in message
ups.com...
thanks for the reply! the master sheet "Day" contains all of my
data/the data is apended to it....then I want the additional 7
worksheets to contain information depending on day e.g. monday, tuesday
etc...the data is naturally copied from the master sheet.

Copy data from sheet 1 to sheet 2 based on day/date
 

Mike,

That sounds like a good idea...i have played about a bit previously
with column "D", but my code wouldnt do the job, its been a while since
programming excel!!

can you make any suggestions for my codE?

jon


Mike Fogleman wrote:
OK. So to keep from copying the same old data each time the macro is run,
would it be OK to mark that row in column AS with a "C" once it has been
copied to another sheet. That way the macro can look for the "C" in column
AS, and if it is not there, evaluate the date in column B, copy the row to
the appropriate sheet, put a "C" at the end of the row, and go to the next
row.
If it is OK to use column AS to mark the row as 'copied', then code can be
written. As an option, any spare column can be used and it can be hidden
from view if the "C" is visually unappealing on the "Day" sheet.

Mike F
wrote in message
ups.com...
thanks for the reply! the master sheet "Day" contains all of my
data/the data is apended to it....then I want the additional 7
worksheets to contain information depending on day e.g. monday, tuesday
etc...the data is naturally copied from the master sheet.

Copy data from sheet 1 to sheet 2 based on day/date
 

mike,

your suggestion sounds good, previously i had tried inserting something
like you suggested in column d (As it is emply) but my programming
skills as rather rusty, can you make any suggestions?

cheers

jon

Copy data from sheet 1 to sheet 2 based on day/date
 

Here is some code that works for me. I started it on row 2 in case you had
headers on row 1.

Option Explicit
Dim Lrow As Long 'finds last row on Day sheet
Dim lRow2 As Long 'finds last row on weekday sheet
Dim rng As Range
Dim c As Range
Dim lDay As Long

Sub CopyToDaysSheet()
Worksheets("Day").Activate
Lrow = Cells(Rows.Count, "B").End(xlUp).Row
Set rng = Range("B2:B" & Lrow) 'change B2 to where your dates start
For Each c In rng
If c.Offset(0, 43).Value = "C" Then 'checks col RS
GoTo 1
Else
lDay = Weekday(c.Value)
Select Case lDay
Case 1
lRow2 = Worksheets("Sunday").Cells(Rows.Count, "B").End(xlUp).Row + 1
c.EntireRow.Copy Destination:=Worksheets("Sunday").Rows(lRow2)
Case 2
lRow2 = Worksheets("Monday").Cells(Rows.Count, "B").End(xlUp).Row + 1
c.EntireRow.Copy Destination:=Worksheets("Monday").Rows(lRow2)
Case 3
lRow2 = Worksheets("Tuesday").Cells(Rows.Count, "B").End(xlUp).Row + 1
c.EntireRow.Copy Destination:=Worksheets("Tuesday").Rows(lRow2)
Case 4
lRow2 = Worksheets("Wednesday").Cells(Rows.Count, "B").End(xlUp).Row + 1
c.EntireRow.Copy Destination:=Worksheets("Wednesday").Rows(lRow2)
Case 5
lRow2 = Worksheets("Thursday").Cells(Rows.Count, "B").End(xlUp).Row + 1
c.EntireRow.Copy Destination:=Worksheets("Thursday").Rows(lRow2)
Case 6
lRow2 = Worksheets("Friday").Cells(Rows.Count, "B").End(xlUp).Row + 1
c.EntireRow.Copy Destination:=Worksheets("Friday").Rows(lRow2)
Case 7
lRow2 = Worksheets("Saturday").Cells(Rows.Count, "B").End(xlUp).Row + 1
c.EntireRow.Copy Destination:=Worksheets("Saturday").Rows(lRow2)
End Select
c.Offset(0, 43).Value = "C" 'marks row as copied
End If
1:
Next c
End Sub

Mike F
wrote in message
oups.com...
mike,

your suggestion sounds good, previously i had tried inserting something
like you suggested in column d (As it is emply) but my programming
skills as rather rusty, can you make any suggestions?

cheers

jon