Need an excel guru....

Soldato
Joined
23 Oct 2002
Posts
3,177
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!
 
Associate
Joined
18 Oct 2002
Posts
564
Location
Lincoln
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
 
Associate
Joined
18 Oct 2002
Posts
564
Location
Lincoln
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
 
Associate
Joined
18 Oct 2002
Posts
564
Location
Lincoln
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
 
Top Bottom