Insert row

[uby]

Hi Everyone!

This is probably a stupid question, but here it goes anyway!

I have 2 workbooks. Workbook 1 gets funded from information in Workbook 2. If Workbook 1 runs out of rows, I want it to insert a row.

Does anyone know a simple way of doing that?

Thanks for any help you can offer!

Uby

 

[SkipVought]

Hi,

If the sheet has 65,536 rows of data, you CANNOT insert another row, since 65,536 is the MAXIMUM number of rows on a sheet.

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[uby]

No. The worksheet does not have that much data. I want for it to reach the total. Once it reaches the total and it still has information to paste, then move the total down by inserting a row before the total.

 

[SkipVought]

Uby,

Put yourself in the place of the poor user of your workbook.

You've got these gob 'n' gobs of data -- row after row. And you want the poor slob to SEARCH for the column totals???

Hey, this is the age of electronic spreadsheets! Totals at the BOTTOM of an list is a vestage of the "good ol' days" of paper 'n' pencil, adding machine 'n' sheet subtotals, reconcilliation, late nights just to find one cent difference!

Be BOLD! Put yer totals right up TOP -- as Bob Uecker would evoke, "Right in the FRONT ROW!"

THEN...

you don't have to worry about "INSERTing" a row -- just put it at the bottom of your table. Don't have to adjust ranges or anything.

Smooooooothe as silk!

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[uby]

That would be nice, right? But unfortunately, I am working with pre-existing workbook, source files, etc. This is what this company has been working with for years. I am trying to automate it with what they have already. The user is internal, not that it makes a difference or anything. I have been asked to work with what is supplied to me.

Please help!

Uby

 

[SkipVought]

Have you tried recording a macro while inserting a row?

Do that and then we'll figgure out what to do with it.

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[devingm]

Hi uby, maybe I'm not understanding you right because this seems really simple. You just write a script to select your second workbook and then insert a row just above the total (or wherever else you want it). So it would be:

Dim WhatEverLineTheTotalIsOn 'set this up by default to equal whatever row the total starts on
Windows("Book1").Activate
Rows(WhatEverLineTheTotalIsOn).Select
Selection.Insert Shift:=xlDown
WhatEverLineTheTotalIsOn = WhatEverLineTheTotalIsOn + 1