Fill Down & Copy

[STEVEB]

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 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]

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 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]

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...