Generate sheet by number of hours on shift.

**coolhit** · 02-10-2010, 06:31 PM

Hi

Please can some assist me. Desparate in need of help.

I have attached the spreadsheet.

User key in the number of hours on shift in cell J46.
And
I want spreadsheet to be generated for that many hours.

http://i47.tinypic.com/rjqnpc.jpg
http://i47.tinypic.com/rjqnpc.jpg

example if cell J46 has 10 then i want row 5 to 14 to be generated.

If cell J46 has value 4 the i want row 5 to 9 to be generated.

Please could some assist me i have give this project in by 9am tomorrow.

Appricate anyone help.

**Richard Buttrey** · 02-10-2010, 06:46 PM

Hi,

Your request isn't clear. What do you mean by

I want spreadsheet to be generated for that many hours.

?

e.g. what are you expecting to happen and where?

Rgds

**coolhit** · 02-10-2010, 06:55 PM

Originally Posted by Richard Buttrey

Hi,

Your request isn't clear. What do you mean by ?

e.g. what are you expecting to happen and where?

Rgds

User keys a value between 1 & 16 in cell J46.

Once the values is keys row 5 to 20 is genetred.

Example J46 has value 10 then row 5 to 14 is generated .

**Richard Buttrey** · 02-10-2010, 07:25 PM

But what do you mean by 'generated'. Do you mean they don't exist before and you want the macro to add new rows or what? All the existing rows have formulae in them so what is there to 'generate'?

Rgds

**coolhit** · 02-11-2010, 06:05 AM

Originally Posted by Richard Buttrey

But what do you mean by 'generated'. Do you mean they don't exist before and you want the macro to add new rows or what? All the existing rows have formulae in them so what is there to 'generate'?

Rgds

Hi

Generate means clear data from B6 to Z20,

Look at SLA cell J46 = 10.

Copy the formula down from row B5 to Z5 to row B6 to Z14 given me 10 rows worths of data.

I don't mind if it a button even.

**Richard Buttrey** · 02-11-2010, 07:04 AM

Hi,

Add the following macro and attach it to a button.

Code:

Sub ClearData()
    Dim iRows As Integer
    iRows = Range("J46")
    If iRows > 15 Then iRows = 15

    Sheet1.Range("B6:Z20").ClearContents
    Range("B5:Z5").Copy
    Range("B5:B" & iRows + 4).PasteSpecial (xlPasteFormulas)

End Sub

Regards