M
mike
Hello, i have been working on a formdata that is filled
out and copied to assigned worksheets (month1, month2..)
here are two macros i have to complete the task.
with help of you guys. (thanks)
Now..what it does is sees the date and checks if passed
the 25th date and then assigns the copy to its month
sheet. april to month4 , may to month5 etc.. I am hapy
with that. However i noticd that some contracts date do
not start till maybe midway thru the year. so when i
want the macro to copy the form data to a worksheet...i
want that worksheet to be the first workshhet of the
contract peroid and commence form there.
ie: lets say the date is april 22nd 2004, so my daily
copied data will go into the 1st worksheet(month1) and be
subsequently added till the 25th day...on the 26th day of
current date; add a new worksheet and call it month2 and
continue on copying each day.
the reason for this is that a contract willl start in the
month signed by board members. from that date forward my
contract to reprt daily data begins and does not stop
until 18 months has passed.
I dont know enuff of this yet to modify on my own
any suggestions to how i modify code.
thanks.
Sub copy_to_sheet()
copy_to = "Month" & Range("month_val").Value
last_used = Sheets(copy_to).Cells.SpecialCells _
(xlCellTypeLastCell).Address
copy_to_address = Rows(Sheets(copy_to).Range _
(last_used).Row + 1).Columns(1).Address
If copy_to_address = "$A$3" Then copy_to_address = "$A$1"
Sheets("form").Range("form_data").Copy
_Destination:=Sheets(copy_to).Range(copy_to_address)
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
this_month = 1
dd = Range("start_date").Value
Do While dd < Range("ref_date").Value
dd = 1 + dd
If Day(dd) = 26 Then this_month = 1 + this_month
Loop
Range("month_val").Value = this_month
End Sub
**Do i add a with statement to add a prperty called
maybe "startwith " or...*** a date value or different
property?
Also could you telll me what this line really means to
us???
last_used = Sheets(copy_to).Cells.SpecialCells _
(xlCellTypeLastCell).Address
copy_to_address = Rows(Sheets(copy_to).Range _
(last_used).Row + 1).Columns(1).Address
If copy_to_address = "$A$3" Then copy_to_address = "$A$1"
One more prob...i noticed if i erased or clear the copied
data ...the code stills copies the new data to the end +
2 rows from where the erased data once occupied. How do i
get it to copy to the top again if it has been erased..
maybe a search line like blank_cell? thanks.
out and copied to assigned worksheets (month1, month2..)
here are two macros i have to complete the task.
with help of you guys. (thanks)
Now..what it does is sees the date and checks if passed
the 25th date and then assigns the copy to its month
sheet. april to month4 , may to month5 etc.. I am hapy
with that. However i noticd that some contracts date do
not start till maybe midway thru the year. so when i
want the macro to copy the form data to a worksheet...i
want that worksheet to be the first workshhet of the
contract peroid and commence form there.
ie: lets say the date is april 22nd 2004, so my daily
copied data will go into the 1st worksheet(month1) and be
subsequently added till the 25th day...on the 26th day of
current date; add a new worksheet and call it month2 and
continue on copying each day.
the reason for this is that a contract willl start in the
month signed by board members. from that date forward my
contract to reprt daily data begins and does not stop
until 18 months has passed.
I dont know enuff of this yet to modify on my own
any suggestions to how i modify code.
thanks.
Sub copy_to_sheet()
copy_to = "Month" & Range("month_val").Value
last_used = Sheets(copy_to).Cells.SpecialCells _
(xlCellTypeLastCell).Address
copy_to_address = Rows(Sheets(copy_to).Range _
(last_used).Row + 1).Columns(1).Address
If copy_to_address = "$A$3" Then copy_to_address = "$A$1"
Sheets("form").Range("form_data").Copy
_Destination:=Sheets(copy_to).Range(copy_to_address)
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
this_month = 1
dd = Range("start_date").Value
Do While dd < Range("ref_date").Value
dd = 1 + dd
If Day(dd) = 26 Then this_month = 1 + this_month
Loop
Range("month_val").Value = this_month
End Sub
**Do i add a with statement to add a prperty called
maybe "startwith " or...*** a date value or different
property?
Also could you telll me what this line really means to
us???
last_used = Sheets(copy_to).Cells.SpecialCells _
(xlCellTypeLastCell).Address
copy_to_address = Rows(Sheets(copy_to).Range _
(last_used).Row + 1).Columns(1).Address
If copy_to_address = "$A$3" Then copy_to_address = "$A$1"
One more prob...i noticed if i erased or clear the copied
data ...the code stills copies the new data to the end +
2 rows from where the erased data once occupied. How do i
get it to copy to the top again if it has been erased..
maybe a search line like blank_cell? thanks.