Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill Down & Copy
Hi Does anyone have any suggestions for a macro that would fill dates dow on a spreadsheet based upon the certain dates entered on a differen sheet? For example on Sheet 1: Cell A1 - Start Date - 1/1/2005 Cell A2 - End Date - 7/31/05 These dates vary & change all the time so I would like a Macro to loo at these dates and on Sheet 2: Column A1 - Fill down all rows(212 days from 1/1/05 to 7/31/05) wit the range of dates on sheet 1(Cell A1 to A2). Column B1 - Copy the Formula ( =TEXT(WEEKDAY(A2),"dddd")) in Column based on the number of rows generated from the start to the end dat (212 rows from 1/1/05 tp 7/31/05). Any help would be greatly appreciated! Thank -- STEVE ----------------------------------------------------------------------- STEVEB's Profile: http://www.excelforum.com/member.php...nfo&userid=187 View this thread: http://www.excelforum.com/showthread.php?threadid=38997 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill Down & Copy
I recorded this code after putting 6/1/05 in A1
Selection.DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:= _ xlDay, Step:=1, Stop:=38534, Trend:=False So - dim x, y x = Sheets("Sheet2").Range("A2") y = 1 ' set y to the number of days between dates. Range("A1")=Sheets("Sheet2").Range("A1") Range("A1").DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:= _ xlDay, Step:=y, Stop:=x, Trend:=Fa -- steveB Remove "AYN" from email to respond "STEVEB" wrote in message ... Hi Does anyone have any suggestions for a macro that would fill dates down on a spreadsheet based upon the certain dates entered on a different sheet? For example on Sheet 1: Cell A1 - Start Date - 1/1/2005 Cell A2 - End Date - 7/31/05 These dates vary & change all the time so I would like a Macro to look at these dates and on Sheet 2: Column A1 - Fill down all rows(212 days from 1/1/05 to 7/31/05) with the range of dates on sheet 1(Cell A1 to A2). Column B1 - Copy the Formula ( =TEXT(WEEKDAY(A2),"dddd")) in Column B based on the number of rows generated from the start to the end date (212 rows from 1/1/05 tp 7/31/05). Any help would be greatly appreciated! Thanks -- STEVEB ------------------------------------------------------------------------ STEVEB's Profile: http://www.excelforum.com/member.php...fo&userid=1872 View this thread: http://www.excelforum.com/showthread...hreadid=389971 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill Down & Copy
Thanks Steve, I was not able to get the code to work. The last part does not appea to run properly. However this would work as well..... Do you know a macro that would: 1) Delete all rows in Column A - Sheet 2 that are greater than Cell A in Sheet 1? Then 2) Copy the formula"=text(weekday(a1), "dddd')" to column B1 throug the remaining rows with a number in column A? Thank -- STEVE ----------------------------------------------------------------------- STEVEB's Profile: http://www.excelforum.com/member.php...nfo&userid=187 View this thread: http://www.excelforum.com/showthread.php?threadid=38997 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill Down & Copy
Steve,
First - correct my error - Range("A1).DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:= _ xlDay, Step:=y, Stop:=x, Trend:=Fa should be Range("A1).DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:= _ xlDay, Step:=y, Stop:=x, Trend:=False If that doesn't work - You may need to select A1 first, than Selection.DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:= _ xlDay, Step:=y, Stop:=x, Trend:=Fa (you rebuild the code by using the recorder) =============================== to delete rows: Dim lrw as Long, rw as Long, x ' <<< define x to match value on Sheet2 lrw = Sheets("Sheet2").= Cells(Rows.COUNT, "A").End(xlUp).Row ' last row in column A Sheet2 x = Sheets("Sheet1").Range("A2") For rw = lrw to 1 step -1 If Sheets("Sheet2").Cells(rw,1) x then Sheets("Sheet2").Rows(x).Delete End If Next Caution: you may have to make provision for mismatch between Cells(rw,1) & x -- steveB Remove "AYN" from email to respond "STEVEB" wrote in message ... Thanks Steve, I was not able to get the code to work. The last part does not appear to run properly. However this would work as well..... Do you know a macro that would: 1) Delete all rows in Column A - Sheet 2 that are greater than Cell A2 in Sheet 1? Then 2) Copy the formula"=text(weekday(a1), "dddd')" to column B1 through the remaining rows with a number in column A? Thanks -- STEVEB ------------------------------------------------------------------------ STEVEB's Profile: http://www.excelforum.com/member.php...fo&userid=1872 View this thread: http://www.excelforum.com/showthread...hreadid=389971 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill Down & Copy
Thanks Steve, I got the fill & copy to work. I really appreciate your help -- STEVE ----------------------------------------------------------------------- STEVEB's Profile: http://www.excelforum.com/member.php...nfo&userid=187 View this thread: http://www.excelforum.com/showthread.php?threadid=38997 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill Down & Copy
Steve,
Glad to hear you got it to work!!! Keep on Exceling... -- steveB Remove "AYN" from email to respond "STEVEB" wrote in message ... Thanks Steve, I got the fill & copy to work. I really appreciate your help! -- STEVEB ------------------------------------------------------------------------ STEVEB's Profile: http://www.excelforum.com/member.php...fo&userid=1872 View this thread: http://www.excelforum.com/showthread...hreadid=389971 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy/Fill increments | Excel Worksheet Functions | |||
Copy and Fill down data | Excel Worksheet Functions | |||
Copy fill | Excel Discussion (Misc queries) | |||
The fill feature in Excel that gives option to fill or copy | New Users to Excel | |||
Fill Default/Fill Copy | Excel Programming |