Dear all;

Is there a worksheet change event that will copy all row formulae to the
next five rows when the bottom row is filled with data?

I have a time/ expense billing database on which each row contains a variety
of Vlookups, validated fields, and other formulae. it is approximately 12
rows wide and an increasing number of rows, expanding downwards.

I am trying to reduce the filesize, because I have currently copied the
formulae etc. down 10,000 rows and the filesize is currently 4mb.

Kind regards
Dylan Dawson

Dylan
You can use a Worksheet_Change event macro if you have at least one
column that contains data, as opposed to a formula. If all your columns
contain formulas, picking up on which cell changed is a bit more
complicated. Assuming that you do have at least one column with data, you
could code something like the following:
Check if the changed cell is in Column (whatever).
If it is, check if the changed cell is in the last occupied row.
If it is, copy/paste whatever you want.
You might also consider changing (by VBA) all your formulas (in that row) to
values once data has been entered if this fits in with your operation. This
will help in keeping your file from ballooning.
Post back if this seems like what you want. HTH Otto
"DDawson" wrote in message
...
Dear all;

Is there a worksheet change event that will copy all row formulae to the
next five rows when the bottom row is filled with data?

I have a time/ expense billing database on which each row contains a
variety
of Vlookups, validated fields, and other formulae. it is approximately 12
rows wide and an increasing number of rows, expanding downwards.

I am trying to reduce the filesize, because I have currently copied the
formulae etc. down 10,000 rows and the filesize is currently 4mb.

Kind regards
Dylan Dawson

Dear Otto,

Column I contains date values and column J contains numbers.

The column range i want to copy paste is A:L

I also have four columns containing data validation dropdowns - perhaps I
could reduce these ranges to cover populated rows only, and make them also
increase with data entry.

I would like to keep the formulae for a while, in case the user makes an
error, because they are read/write protected. However it would be an idea if
for example, as a new row is entered the row 10 above is changed to values.

Hope you have a great Christmas!
Kind regards
Dylan

"Otto Moehrbach" wrote:

Dylan
You can use a Worksheet_Change event macro if you have at least one
column that contains data, as opposed to a formula. If all your columns
contain formulas, picking up on which cell changed is a bit more
complicated. Assuming that you do have at least one column with data, you
could code something like the following:
Check if the changed cell is in Column (whatever).
If it is, check if the changed cell is in the last occupied row.
If it is, copy/paste whatever you want.
You might also consider changing (by VBA) all your formulas (in that row) to
values once data has been entered if this fits in with your operation. This
will help in keeping your file from ballooning.
Post back if this seems like what you want. HTH Otto
"DDawson" wrote in message
...
Dear all;

Is there a worksheet change event that will copy all row formulae to the
next five rows when the bottom row is filled with data?

I have a time/ expense billing database on which each row contains a
variety
of Vlookups, validated fields, and other formulae. it is approximately 12
rows wide and an increasing number of rows, expanding downwards.

I am trying to reduce the filesize, because I have currently copied the
formulae etc. down 10,000 rows and the filesize is currently 4mb.

Kind regards
Dylan Dawson

Dylan
Yes, I can write the code to put the Data Validations into the new row.
And yes, I can write the code to replace with values the row 10 up.
We (you and I) have to come up with an event to trigger the macro to run.
From what you have said, the event would be the entry of data into one of
the columns (in that last row). That cell must not be one that has a
formula in it. It can be a cell that has Data Validation as well as just a
plain data entry cell. You know your operation. What column?
Otto
"DDawson" wrote in message
...
Dear Otto,

Column I contains date values and column J contains numbers.

The column range i want to copy paste is A:L

I also have four columns containing data validation dropdowns - perhaps I
could reduce these ranges to cover populated rows only, and make them also
increase with data entry.

I would like to keep the formulae for a while, in case the user makes an
error, because they are read/write protected. However it would be an idea
if
for example, as a new row is entered the row 10 above is changed to
values.

Hope you have a great Christmas!
Kind regards
Dylan

"Otto Moehrbach" wrote:

Dylan
You can use a Worksheet_Change event macro if you have at least one
column that contains data, as opposed to a formula. If all your columns
contain formulas, picking up on which cell changed is a bit more
complicated. Assuming that you do have at least one column with data,
you
could code something like the following:
Check if the changed cell is in Column (whatever).
If it is, check if the changed cell is in the last occupied row.
If it is, copy/paste whatever you want.
You might also consider changing (by VBA) all your formulas (in that row)
to
values once data has been entered if this fits in with your operation.
This
will help in keeping your file from ballooning.
Post back if this seems like what you want. HTH Otto
"DDawson" wrote in message
...
Dear all;

Is there a worksheet change event that will copy all row formulae to
the
next five rows when the bottom row is filled with data?

I have a time/ expense billing database on which each row contains a
variety
of Vlookups, validated fields, and other formulae. it is approximately
12
rows wide and an increasing number of rows, expanding downwards.

I am trying to reduce the filesize, because I have currently copied the
formulae etc. down 10,000 rows and the filesize is currently 4mb.

Kind regards
Dylan Dawson

Column I please

"Otto Moehrbach" wrote:

Dylan
Yes, I can write the code to put the Data Validations into the new row.
And yes, I can write the code to replace with values the row 10 up.
We (you and I) have to come up with an event to trigger the macro to run.
From what you have said, the event would be the entry of data into one of
the columns (in that last row). That cell must not be one that has a
formula in it. It can be a cell that has Data Validation as well as just a
plain data entry cell. You know your operation. What column?
Otto
"DDawson" wrote in message
...
Dear Otto,

Column I contains date values and column J contains numbers.

The column range i want to copy paste is A:L

I also have four columns containing data validation dropdowns - perhaps I
could reduce these ranges to cover populated rows only, and make them also
increase with data entry.

I would like to keep the formulae for a while, in case the user makes an
error, because they are read/write protected. However it would be an idea
if
for example, as a new row is entered the row 10 above is changed to
values.

Hope you have a great Christmas!
Kind regards
Dylan

"Otto Moehrbach" wrote:

Dylan
You can use a Worksheet_Change event macro if you have at least one
column that contains data, as opposed to a formula. If all your columns
contain formulas, picking up on which cell changed is a bit more
complicated. Assuming that you do have at least one column with data,
you
could code something like the following:
Check if the changed cell is in Column (whatever).
If it is, check if the changed cell is in the last occupied row.
If it is, copy/paste whatever you want.
You might also consider changing (by VBA) all your formulas (in that row)
to
values once data has been entered if this fits in with your operation.
This
will help in keeping your file from ballooning.
Post back if this seems like what you want. HTH Otto
"DDawson" wrote in message
...
Dear all;

Is there a worksheet change event that will copy all row formulae to
the
next five rows when the bottom row is filled with data?

I have a time/ expense billing database on which each row contains a
variety
of Vlookups, validated fields, and other formulae. it is approximately
12
rows wide and an increasing number of rows, expanding downwards.

I am trying to reduce the filesize, because I have currently copied the
formulae etc. down 10,000 rows and the filesize is currently 4mb.

Kind regards
Dylan Dawson

Daryl
I should have asked you this before - What columns have the Data
Validation cells and what columns have formulas? Otto
"DDawson" wrote in message
...
Column I please

"Otto Moehrbach" wrote:

Dylan
Yes, I can write the code to put the Data Validations into the new
row.
And yes, I can write the code to replace with values the row 10 up.
We (you and I) have to come up with an event to trigger the macro to run.
From what you have said, the event would be the entry of data into one of
the columns (in that last row). That cell must not be one that has a
formula in it. It can be a cell that has Data Validation as well as just
a
plain data entry cell. You know your operation. What column?
Otto
"DDawson" wrote in message
...
Dear Otto,

Column I contains date values and column J contains numbers.

The column range i want to copy paste is A:L

I also have four columns containing data validation dropdowns - perhaps
I
could reduce these ranges to cover populated rows only, and make them
also
increase with data entry.

I would like to keep the formulae for a while, in case the user makes
an
error, because they are read/write protected. However it would be an
idea
if
for example, as a new row is entered the row 10 above is changed to
values.

Hope you have a great Christmas!
Kind regards
Dylan

"Otto Moehrbach" wrote:

Dylan
You can use a Worksheet_Change event macro if you have at least
one
column that contains data, as opposed to a formula. If all your
columns
contain formulas, picking up on which cell changed is a bit more
complicated. Assuming that you do have at least one column with data,
you
could code something like the following:
Check if the changed cell is in Column (whatever).
If it is, check if the changed cell is in the last occupied row.
If it is, copy/paste whatever you want.
You might also consider changing (by VBA) all your formulas (in that
row)
to
values once data has been entered if this fits in with your operation.
This
will help in keeping your file from ballooning.
Post back if this seems like what you want. HTH Otto
"DDawson" wrote in message
...
Dear all;

Is there a worksheet change event that will copy all row formulae to
the
next five rows when the bottom row is filled with data?

I have a time/ expense billing database on which each row contains a
variety
of Vlookups, validated fields, and other formulae. it is
approximately
12
rows wide and an increasing number of rows, expanding downwards.

I am trying to reduce the filesize, because I have currently copied
the
formulae etc. down 10,000 rows and the filesize is currently 4mb.

Kind regards
Dylan Dawson