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 :)

"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 :)