Copying & Inserting Rows w/o Affecting other Rows Etc.

[LRay67]

I have a spreadsheet that a user can click on an option button and copy and
insert rows. Problem is that when this happens it moves all the other data
down another row or two depending on how many times they insert rows.

This affects other macros that have the same capability to copy and insert
rows in other areas of the spreadsheet. I can not figure out how to add
additional rows and maintain the rest of the sheets format throughout the
spreadsheet.

This is the code I have to copy and insert row on one section
Example: User wants another Email Request line they click on the option
button to insert the copied cells.

Sub PIMUserRequest()
'
Range("A16:V16").Select
Selection.Copy
Rows("17:17").Select
Range("C17").Activate
Selection.Insert Shift:=xlDown
Rows("17:17").RowHeight = 15.75
End Sub

Please HELP

Linda

 

[JLatham]

You're going to have to learn to be a little more 'robust' in creating your
macros. I suspect that these have been recorded while you took some actions.

A recorded macro will always capture the specific cell, row or column
references that were used during the recording. And then it faithfully
performs exactly those same operations in the future.

For those times when you want to do the same thing, but with different
areas, you'll have to learn to modify your recorded macros.

Chip Pearson has some stuff on his web site that may help you:
http://www.cpearson.com/excel/MainPage.aspx
as does Debra Dalgleish at hers:
http://www.contextures.com/
and there are many more such sources.

I might also suggest grabbing a book on VBA programming for Excel and
digging into it some. Be looking especially for topics like named ranges and
the use of properties like Range("A1").End(xlUp).Row [and A1 is just an
address I picked, and .Row can be replaced by any of several other
properties].

 

[Gord Dibben]

If you insert a row or rows, the rows below have to go somewhere.

You can insert cells and shift down just for the column without affecting
adjacent columns or rest of row but cells below the inserted cell have to
move down in that column.


Gord Dibben MS Excel MVP