macro to insert pair of formulae in x number of rows -URGENT!!!

I've created a simple macro using "Insert Copied Cells" that copies and
pastes x number of rows of data at the top of an existing table of data. In
the table, there are five columns of data pasted in, and then two columns on
the right in which I add a pair of simple formulae (the same two formulae
repeated downwards in all rows).

I'd like these to appear automatically (via a macro) into the newly inserted
x number of rows (without knowing the value of x) after pasting in the data
in the first five columns.

Any ideas??? Is there a way to autofill down until you hit a non-blank cell
(I can insert the two formulae in the cells above the paste area)? or a way
to tell a macro to highlight downwards until it reaches a non-blank cell?

Thank you so much!!

Marika

Hi Marika

Try this if data in Column A does not have blank cells in it

Sub FillDown()

Range("F2:G2").AutoFill Destination:=Range("F2:G" &
Range("A1000").End(xlUp).Row)

End Sub

Watch out for word wrap above should be one line
A1000 can be changed to A10000 or 65000.
HTH

Regards,
Aussie Bob C.

"marika1981" wrote:

> I've created a simple macro using "Insert Copied Cells" that copies and
> pastes x number of rows of data at the top of an existing table of data. In
> the table, there are five columns of data pasted in, and then two columns on
> the right in which I add a pair of simple formulae (the same two formulae
> repeated downwards in all rows).
>
> I'd like these to appear automatically (via a macro) into the newly inserted
> x number of rows (without knowing the value of x) after pasting in the data
> in the first five columns.
>
> Any ideas??? Is there a way to autofill down until you hit a non-blank cell
> (I can insert the two formulae in the cells above the paste area)? or a way
> to tell a macro to highlight downwards until it reaches a non-blank cell?
>
> Thank you so much!!
>
> Marika
>
>

Dear Bob,

Thanks SO much for your input - I really appreciate it!! The code you
offered ALMOST did the trick, but there's still one problem: it autofills all
the way down the table until the absolute last row. I'm trying to get it to
autofill down through the last blank row (basically the same number of rows
that were inserted in the previous step).

So, in a nutshell, the code needs to be adjusted so it autofills only
through the blank cells to the first cell of text in the column, not the
last. Any ideas???

Thanks again SO very much!

Marika


"Robert Christie" wrote:

> Hi Marika
>
> Try this if data in Column A does not have blank cells in it
>
> Sub FillDown()
>
> Range("F2:G2").AutoFill Destination:=Range("F2:G" &
> Range("A1000").End(xlUp).Row)
>
> End Sub
>
> Watch out for word wrap above should be one line
> A1000 can be changed to A10000 or 65000.
> HTH
>
> Regards,
> Aussie Bob C.
>
> "marika1981" wrote:
>
> > I've created a simple macro using "Insert Copied Cells" that copies and
> > pastes x number of rows of data at the top of an existing table of data. In
> > the table, there are five columns of data pasted in, and then two columns on
> > the right in which I add a pair of simple formulae (the same two formulae
> > repeated downwards in all rows).
> >
> > I'd like these to appear automatically (via a macro) into the newly inserted
> > x number of rows (without knowing the value of x) after pasting in the data
> > in the first five columns.
> >
> > Any ideas??? Is there a way to autofill down until you hit a non-blank cell
> > (I can insert the two formulae in the cells above the paste area)? or a way
> > to tell a macro to highlight downwards until it reaches a non-blank cell?
> >
> > Thank you so much!!
> >
> > Marika
> >
> >

Sorry Marika

i really don't understand what you are trying to do.
Where are the formulas to be placed?
Along side the inserted data only?
Where do they start and finish?
Do they stop at the start of the existing data?
What is the data layout, are both new and old 5 columns wide?
Where are the blank rows between the old and new data?
Aussie Bob C.

"marika1981" wrote:

> Dear Bob,
>
> Thanks SO much for your input - I really appreciate it!! The code you
> offered ALMOST did the trick, but there's still one problem: it autofills all
> the way down the table until the absolute last row. I'm trying to get it to
> autofill down through the last blank row (basically the same number of rows
> that were inserted in the previous step).
>
> So, in a nutshell, the code needs to be adjusted so it autofills only
> through the blank cells to the first cell of text in the column, not the
> last. Any ideas???
>
> Thanks again SO very much!
>
> Marika
>
>
> "Robert Christie" wrote:
>
> > Hi Marika
> >
> > Try this if data in Column A does not have blank cells in it
> >
> > Sub FillDown()
> >
> > Range("F2:G2").AutoFill Destination:=Range("F2:G" &
> > Range("A1000").End(xlUp).Row)
> >
> > End Sub
> >
> > Watch out for word wrap above should be one line
> > A1000 can be changed to A10000 or 65000.
> > HTH
> >
> > Regards,
> > Aussie Bob C.
> >
> > "marika1981" wrote:
> >
> > > I've created a simple macro using "Insert Copied Cells" that copies and
> > > pastes x number of rows of data at the top of an existing table of data. In
> > > the table, there are five columns of data pasted in, and then two columns on
> > > the right in which I add a pair of simple formulae (the same two formulae
> > > repeated downwards in all rows).
> > >
> > > I'd like these to appear automatically (via a macro) into the newly inserted
> > > x number of rows (without knowing the value of x) after pasting in the data
> > > in the first five columns.
> > >
> > > Any ideas??? Is there a way to autofill down until you hit a non-blank cell
> > > (I can insert the two formulae in the cells above the paste area)? or a way
> > > to tell a macro to highlight downwards until it reaches a non-blank cell?
> > >
> > > Thank you so much!!
> > >
> > > Marika
> > >
> > >

Dear Bob,

Apologies for the confusion. I'm creating a spreadsheet for a non-Excel
end-user, thus trying to automate the process almost entirely. Each week,
they'll insert about 50 rows of raw data in five columns at the TOP of the
datatable using a macro i created based on InsertCopiedCells. However, in
the two columns to the right of the data there need to be two simple formulae
added after any new data is inserted, repeating the whole way down the entire
table.

So, for example, let's say columns 10-450 have five columns of data and two
columns of formulae. Then User X inserts 45 more rows of data at the top.
Now rows 10-55 have five columns of data, but no formulae in columns 6 and 7.
Starting in row 56, the rows have the seven complete columns. I'm trying to
write a macro that will highlight the range F10:G55 (though the number of new
rows each week will vary) and insert the fomulae in the blanks. ***Note, I
do have a hidden row above (row 9) that includes the formulae to better
enable autofill.

Any ideas you have would be so much appreciated!!

THANK YOU SO MUCH!!!!!

Marika



"Robert Christie" wrote:

> Sorry Marika
>
> i really don't understand what you are trying to do.
> Where are the formulas to be placed?
> Along side the inserted data only?
> Where do they start and finish?
> Do they stop at the start of the existing data?
> What is the data layout, are both new and old 5 columns wide?
> Where are the blank rows between the old and new data?
> Aussie Bob C.
>
> "marika1981" wrote:
>
> > Dear Bob,
> >
> > Thanks SO much for your input - I really appreciate it!! The code you
> > offered ALMOST did the trick, but there's still one problem: it autofills all
> > the way down the table until the absolute last row. I'm trying to get it to
> > autofill down through the last blank row (basically the same number of rows
> > that were inserted in the previous step).
> >
> > So, in a nutshell, the code needs to be adjusted so it autofills only
> > through the blank cells to the first cell of text in the column, not the
> > last. Any ideas???
> >
> > Thanks again SO very much!
> >
> > Marika
> >
> >
> > "Robert Christie" wrote:
> >
> > > Hi Marika
> > >
> > > Try this if data in Column A does not have blank cells in it
> > >
> > > Sub FillDown()
> > >
> > > Range("F2:G2").AutoFill Destination:=Range("F2:G" &
> > > Range("A1000").End(xlUp).Row)
> > >
> > > End Sub
> > >
> > > Watch out for word wrap above should be one line
> > > A1000 can be changed to A10000 or 65000.
> > > HTH
> > >
> > > Regards,
> > > Aussie Bob C.
> > >
> > > "marika1981" wrote:
> > >
> > > > I've created a simple macro using "Insert Copied Cells" that copies and
> > > > pastes x number of rows of data at the top of an existing table of data. In
> > > > the table, there are five columns of data pasted in, and then two columns on
> > > > the right in which I add a pair of simple formulae (the same two formulae
> > > > repeated downwards in all rows).
> > > >
> > > > I'd like these to appear automatically (via a macro) into the newly inserted
> > > > x number of rows (without knowing the value of x) after pasting in the data
> > > > in the first five columns.
> > > >
> > > > Any ideas??? Is there a way to autofill down until you hit a non-blank cell
> > > > (I can insert the two formulae in the cells above the paste area)? or a way
> > > > to tell a macro to highlight downwards until it reaches a non-blank cell?
> > > >
> > > > Thank you so much!!
> > > >
> > > > Marika
> > > >
> > > >

see possible answer to second posting of this question.

--
Regards,
Tom Ogilvy

"marika1981" <marika1981@discussions.microsoft.com> wrote in message
news:C85424B3-00BC-4C6B-BE82-69F929D4A3EB@microsoft.com...
> Dear Bob,
>
> Apologies for the confusion. I'm creating a spreadsheet for a non-Excel
> end-user, thus trying to automate the process almost entirely. Each week,
> they'll insert about 50 rows of raw data in five columns at the TOP of the
> datatable using a macro i created based on InsertCopiedCells. However, in
> the two columns to the right of the data there need to be two simple
formulae
> added after any new data is inserted, repeating the whole way down the
entire
> table.
>
> So, for example, let's say columns 10-450 have five columns of data and
two
> columns of formulae. Then User X inserts 45 more rows of data at the top.
> Now rows 10-55 have five columns of data, but no formulae in columns 6 and
7.
> Starting in row 56, the rows have the seven complete columns. I'm trying
to
> write a macro that will highlight the range F10:G55 (though the number of
new
> rows each week will vary) and insert the fomulae in the blanks. ***Note,
I
> do have a hidden row above (row 9) that includes the formulae to better
> enable autofill.
>
> Any ideas you have would be so much appreciated!!
>
> THANK YOU SO MUCH!!!!!
>
> Marika
>
>
>
> "Robert Christie" wrote:
>
> > Sorry Marika
> >
> > i really don't understand what you are trying to do.
> > Where are the formulas to be placed?
> > Along side the inserted data only?
> > Where do they start and finish?
> > Do they stop at the start of the existing data?
> > What is the data layout, are both new and old 5 columns wide?
> > Where are the blank rows between the old and new data?
> > Aussie Bob C.
> >
> > "marika1981" wrote:
> >
> > > Dear Bob,
> > >
> > > Thanks SO much for your input - I really appreciate it!! The code you
> > > offered ALMOST did the trick, but there's still one problem: it
autofills all
> > > the way down the table until the absolute last row. I'm trying to get
it to
> > > autofill down through the last blank row (basically the same number of
rows
> > > that were inserted in the previous step).
> > >
> > > So, in a nutshell, the code needs to be adjusted so it autofills only
> > > through the blank cells to the first cell of text in the column, not
the
> > > last. Any ideas???
> > >
> > > Thanks again SO very much!
> > >
> > > Marika
> > >
> > >
> > > "Robert Christie" wrote:
> > >
> > > > Hi Marika
> > > >
> > > > Try this if data in Column A does not have blank cells in it
> > > >
> > > > Sub FillDown()
> > > >
> > > > Range("F2:G2").AutoFill Destination:=Range("F2:G" &
> > > > Range("A1000").End(xlUp).Row)
> > > >
> > > > End Sub
> > > >
> > > > Watch out for word wrap above should be one line
> > > > A1000 can be changed to A10000 or 65000.
> > > > HTH
> > > >
> > > > Regards,
> > > > Aussie Bob C.
> > > >
> > > > "marika1981" wrote:
> > > >
> > > > > I've created a simple macro using "Insert Copied Cells" that
copies and
> > > > > pastes x number of rows of data at the top of an existing table of
data. In
> > > > > the table, there are five columns of data pasted in, and then two
columns on
> > > > > the right in which I add a pair of simple formulae (the same two
formulae
> > > > > repeated downwards in all rows).
> > > > >
> > > > > I'd like these to appear automatically (via a macro) into the
newly inserted
> > > > > x number of rows (without knowing the value of x) after pasting in
the data
> > > > > in the first five columns.
> > > > >
> > > > > Any ideas??? Is there a way to autofill down until you hit a
non-blank cell
> > > > > (I can insert the two formulae in the cells above the paste area)?
or a way
> > > > > to tell a macro to highlight downwards until it reaches a
non-blank cell?
> > > > >
> > > > > Thank you so much!!
> > > > >
> > > > > Marika
> > > > >
> > > > >

Thanks very much, Tom.....your suggestions are ALWAYS much appreciated and
right on target.
Your SUMPRDUCT solution to adding data with multiple adjacent column
conditions is the greatest trick i've learned in a really long time!

Regards,

Marika....




"Tom Ogilvy" wrote:

> see possible answer to second posting of this question.
>
> --
> Regards,
> Tom Ogilvy
>
> "marika1981" <marika1981@discussions.microsoft.com> wrote in message
> news:C85424B3-00BC-4C6B-BE82-69F929D4A3EB@microsoft.com...
> > Dear Bob,
> >
> > Apologies for the confusion. I'm creating a spreadsheet for a non-Excel
> > end-user, thus trying to automate the process almost entirely. Each week,
> > they'll insert about 50 rows of raw data in five columns at the TOP of the
> > datatable using a macro i created based on InsertCopiedCells. However, in
> > the two columns to the right of the data there need to be two simple
> formulae
> > added after any new data is inserted, repeating the whole way down the
> entire
> > table.
> >
> > So, for example, let's say columns 10-450 have five columns of data and
> two
> > columns of formulae. Then User X inserts 45 more rows of data at the top.
> > Now rows 10-55 have five columns of data, but no formulae in columns 6 and
> 7.
> > Starting in row 56, the rows have the seven complete columns. I'm trying
> to
> > write a macro that will highlight the range F10:G55 (though the number of
> new
> > rows each week will vary) and insert the fomulae in the blanks. ***Note,
> I
> > do have a hidden row above (row 9) that includes the formulae to better
> > enable autofill.
> >
> > Any ideas you have would be so much appreciated!!
> >
> > THANK YOU SO MUCH!!!!!
> >
> > Marika
> >
> >
> >
> > "Robert Christie" wrote:
> >
> > > Sorry Marika
> > >
> > > i really don't understand what you are trying to do.
> > > Where are the formulas to be placed?
> > > Along side the inserted data only?
> > > Where do they start and finish?
> > > Do they stop at the start of the existing data?
> > > What is the data layout, are both new and old 5 columns wide?
> > > Where are the blank rows between the old and new data?
> > > Aussie Bob C.
> > >
> > > "marika1981" wrote:
> > >
> > > > Dear Bob,
> > > >
> > > > Thanks SO much for your input - I really appreciate it!! The code you
> > > > offered ALMOST did the trick, but there's still one problem: it
> autofills all
> > > > the way down the table until the absolute last row. I'm trying to get
> it to
> > > > autofill down through the last blank row (basically the same number of
> rows
> > > > that were inserted in the previous step).
> > > >
> > > > So, in a nutshell, the code needs to be adjusted so it autofills only
> > > > through the blank cells to the first cell of text in the column, not
> the
> > > > last. Any ideas???
> > > >
> > > > Thanks again SO very much!
> > > >
> > > > Marika
> > > >
> > > >
> > > > "Robert Christie" wrote:
> > > >
> > > > > Hi Marika
> > > > >
> > > > > Try this if data in Column A does not have blank cells in it
> > > > >
> > > > > Sub FillDown()
> > > > >
> > > > > Range("F2:G2").AutoFill Destination:=Range("F2:G" &
> > > > > Range("A1000").End(xlUp).Row)
> > > > >
> > > > > End Sub
> > > > >
> > > > > Watch out for word wrap above should be one line
> > > > > A1000 can be changed to A10000 or 65000.
> > > > > HTH
> > > > >
> > > > > Regards,
> > > > > Aussie Bob C.
> > > > >
> > > > > "marika1981" wrote:
> > > > >
> > > > > > I've created a simple macro using "Insert Copied Cells" that
> copies and
> > > > > > pastes x number of rows of data at the top of an existing table of
> data. In
> > > > > > the table, there are five columns of data pasted in, and then two
> columns on
> > > > > > the right in which I add a pair of simple formulae (the same two
> formulae
> > > > > > repeated downwards in all rows).
> > > > > >
> > > > > > I'd like these to appear automatically (via a macro) into the
> newly inserted
> > > > > > x number of rows (without knowing the value of x) after pasting in
> the data
> > > > > > in the first five columns.
> > > > > >
> > > > > > Any ideas??? Is there a way to autofill down until you hit a
> non-blank cell
> > > > > > (I can insert the two formulae in the cells above the paste area)?
> or a way
> > > > > > to tell a macro to highlight downwards until it reaches a
> non-blank cell?
> > > > > >
> > > > > > Thank you so much!!
> > > > > >
> > > > > > Marika
> > > > > >
> > > > > >
>
>
>

"marika1981" wrote:

> I've created a simple macro using "Insert Copied Cells" that copies and
> pastes x number of rows of data at the top of an existing table of data. In
> the table, there are five columns of data pasted in, and then two columns on
> the right in which I add a pair of simple formulae (the same two formulae
> repeated downwards in all rows).
>
> I'd like these to appear automatically (via a macro) into the newly inserted
> x number of rows (without knowing the value of x) after pasting in the data
> in the first five columns.
>
> Any ideas??? Is there a way to autofill down until you hit a non-blank cell
> (I can insert the two formulae in the cells above the paste area)? or a way
> to tell a macro to highlight downwards until it reaches a non-blank cell?
>
> Thank you so much!!
>
> Marika
>
>