Copy, Paste Special and Insert

[athard]

Hi,

I have a unique problem that I have solved half way through.

My spreadsheet has 2 tabs. The first tab has some info that changes frequently. The second tab is where the revised info needs to be inserted (not pasted) into row 3 to 22.

So essentially a person goes into tab 1 and enters some info, which is processed through formulas. Then, when they hit a button, the macro will copy cells A10 to A29 and paste special and Insert it into the second tab, starting at A3.

I got it to paste special but not insert, it writes over anything else posted there.

Can somebody help?

Thanks in advance.

[mdmackillop]

I think you need to do it in 2 steps
[VBA] Sheets(1).Range("A10:A29").Copy
Sheets("Data").Range("A4").Insert Shift:=xlDown
Sheets("Data").Range("A4").PasteSpecial Paste:=xlPasteValues
[/VBA]

[RonMcK]

Malcolm,

A question for my understanding.

Am I interpreting your code correctly? The copy grabs all rows from A10 to A29 (19 rows) by the width of the worksheet (all columns out to XFD (if in XL 2007))? And, then, it inserts this rectangular block into the target starting on the row before A4 (so A3, as stipulated). Finally, you paste special > values so we have the resulting data and no formulas. Right?

Why not resize that block based upon the last column actually used?

Just wondering.

Thanks,

[mdmackillop]

Hi Ron
While there may be an overhead in copying entire rows, for a single operation, I don't think it is significant, If there was an issue, then certainly try limiting the copy/paste as you described.
PasteSpecial was identified in the question as a requirement

[RonMcK]

Malcolm,

Thanks,