Need an excel guru....

[realBabelfish]

OK, i have taken on a new role where i am now pricing tenders at work. I hate excel so i tend to have avoided it like the plague over the years. Im using office 2007 with winpro7 64 bit FYI

Ok, so what i need help with is simplifying this ball-aching task and to avoid me possibly making a mistake too.

We have a template setup with around 6 columns shown and around 40+ hidden with various calculations in.

When we get a pricing document in i copy the first few columns into our template then i have to insert rows into excel. This is often in the region of 200-1000 new rows. Im sure there used to be ‘insert x rows’ button? Now all i can do is insert 1 row then CTRL+Y to insert subsequent rows. This is tedious for 1000 rows and i have no idea of how many i have inserted. Failure to do the right amount overwrites important tender information at the bottom on the excel document.

Is there a way to speed this up? I have checked the selected rows copied in the client excel file yet it doesn’t tell me how many rows have been copied?!?! It tells me a different number! Grrr ( i figured out how to have 2 separate excel windows open as i run 2 x 24” monitors and i hate having to open to wide documents in 1 window)

I then need to get the formulas to copy down into the new rows (im sure excel used to be able to do this automatically!!) but with 40+ colums even if i shrink the doc really small i cant see columns very clearly and im worried i might **** it up when i do this in the future.

Ok, i have just realised i can copy and paste the rows for the formulas to copy across, is there a way i can get it to paste in, say, 400 of these rows?

Im realising why i hate excel now.... thanks in advance!

 

[HyperSonic]

create a button and add this code to it:

Sub insertRows()
On Error Resume Next
ActiveCell.Resize(Application.InputBox("Number of New Rows?", Default:="1", Type:=1), 1).EntireRow.Insert
On Error GoTo 0
End Sub

when the button is clicked it will bring up an input box & you enter how many rows you need then click ok, it will put the rows arfter the selected cell

 

[HyperSonic]

that should read above the selected cell

 

[HyperSonic]

as for filling the cells, it can be done in code but much easier to do this:

using the box in the top left that tells you which cell you have selected, type in the cell that contains the formula then : and then the last cell you want to autofill to (eg b10:b30). press enter and this will highlight that range of cells.

then simply press CRTL+D to fill the cells

 

[HyperSonic]

if you have 40 columns to do (and they're next to each other) you can do them in one go

say you have 5 formulas in cells a1 to e1 and want to copy the formulas down 9 more rows:

using the name box in the top left enter: a1:e10
then CTRL + D

without seeing the workbook it'd be hard to write a vba script to use on a button

 

[realBabelfish]

cheers guys. i will have a look at this.