Insert new row & prompt to ask how many rows to insert

[lady_kay]

Hi,

Hope you can help - im still just a learner in the world of macros & VBA code!

Im trying to insert a new row in my attached excel spreadsheet with the above rows formatting & validation rules, i have managed to copy the validation and formatting of a previous row but stuck on ensuring the last row is always copied - it picks up row 11 every time instead of moving down the page. I also want the macro to ask me how many rows to insert so i can add, say 10 rows at a time. Some of the columns are hidden & the cells with validation are highlighted in green with the list options to the right of the table.

Attachment 225095

I have tried previous explanations such as the below but receive error codes.

http://www.mrexcel.com/forum/excel-q...above-row.html

Thanks

[AndyLitch]

Hi.

Need you to do a couple of things...
1.Insert a new worksheet.
2.Copy row 3 from your main sheet and paste it into row 1 of your new worksheet
3.Hide the new worksheet - you don't need it anymore
4. Replace your code with this

Please Login or Register  to view this content.

I think that's what you're looking for

[lady_kay]

Hey andy - thanks for the quick reply!

I have followed the instructions above but have come across an error, can you help resolve?

The debug error is 'run-time error 424' object required. The code highlights Sheet1.Rows("1:1").Copy as being the rogue entry.

What checks can i carry out to work this out? The new worksheet doesnt have any spaces i.e. Sheet1

[AndyLitch]

My mistake Kay - don't make the sheet invisible ........... I can be really stupid sometimes.

[lady_kay]

hey no problems - youre helping me massively! i tried it with the sheet visible too but same message... its nearly there

[AB33]

Please Login or Register  to view this content.

OR

Please Login or Register  to view this content.

[AndyLitch]

It's a bit strange.. I just went through the process i described to you and it works ok for me. I can only assume it's because your on Excel 2003 while I'm using 2007.

Try this alternative

Please Login or Register  to view this content.

[AB33]

Andy,
The OP does not have a sheet called sheet1.
You have either worksheets(1), or sheets("Request Tracker")

[AndyLitch]

I asked her to insert one at step2... Just to keep the template row isolated

[JosephP]

but the new sheet's codename may not be Sheet1 ;-)

[AB33]

Jay,
You always come up with some amazing quotes. I am going to use "on error goto bed", but instead of goto bed, would use goto askjay

[lady_kay]

hi guys,

thanks so much for taking time to offer your suggestions, i've almost got it working, i created a new workbook which seemed to help... just a couple of changes you could help me with if you dont mind??

This is the code that is working without debug errors:

Sub Insert_New_Row()
Dim Y As Integer, Endx As Integer

Endx = InputBox("How many rows?", "INSERT", 10)
If Endx = 0 Then Endx = 1

For Y = 1 To Endx
Rows("6:6").Insert Shift:=xlDown
Sheets("Sheet1").Rows("1:1").Copy
Rows("6:6").Select
ActiveSheet.Paste
Next Y

End Sub

1. However, the inserted rows are placed below the grey line - can the inserted rows always be inserted immediately above the grey line?

2. The inserted rows are making the validation list split (i hadn't thought of this until now) so ideally it should be the cells that need to be copied rather than an entire row. The validation list is also changing on a daily basis so the copied cells should account for this - should the copied cells therefore not come from the last row (col A to U) of the Request Tracker worksheet?

3. The inserted rows contain the values of the copied row, they should only contain the validation and format.

I have attached a revised spreadsheet to demonstrate the above.


Thanks again for your help - im learning loads from this

[AndyLitch]

Request Tracker - VBA help 2.xls

The top 12 rows can be easily hidden from normal users view.
Last stage is to check the data validation works correctly when lists are amended etc