Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy rows from one Data sheet to another sheet based on cell conte | Excel Discussion (Misc queries) | |||
Copy from one Sheet and paste on another sheet based on condition | Excel Discussion (Misc queries) | |||
Search for rows in one sheet and copy into another sheet based on customer id | Excel Worksheet Functions | |||
Formula Help - Copy data from one sheet to another based on criter | Excel Worksheet Functions | |||
how to find and copy values on sheet 2, based on a list on sheet 1 | Excel Programming |