Fill Down & Copy

[STEVEB]

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

[STEVE BELL]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[STEVEB]

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

[STEVE BELL]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[STEVEB]

Thanks Steve,

I got the fill & copy to work. I really appreciate your help!

[STEVE BELL]

Steve,

Glad to hear you got it to work!!!

Keep on Exceling...

--
steveB

Remove "AYN" from email to respond
"STEVEB" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>