via code to use to 6, 8, 12 sheets

[4hoteliers]

I have the below via code to insert rows above the last cell with value in 3 different workbooks.
These workbooks contain 6, 8, 12 sheets.
How can I use this to add rows to all sheets in each workbook???
I an using this now on each sheet by changing the Sheet name (Sheet1 to Sheet2 then to Sheet3 and so on....)
Thank you i advance for your help,
Michalis




Sub Insert_Rows()
Application.ScreenUpdating = False
Dim Last_Row As Long
With Sheets("Sheet1")
Last_Row = Range("A" & Rows.Count).End(xlUp).Row
Range(.Cells(Last_Row, 1), .Cells(Last_Row + 4, 1)).EntireRow.Insert
End With
Application.ScreenUpdating = True
End Sub

[snb]

- Welcome !
- Please use code tags
-'inserting rows' isn't a clever thing to do in VBA: a worksheet always contains the same amount of rows/columns: rows.count

You could use:


Sub M_snb()
for j=1 to 3
for each sh in workbooks(j).sheets
sh.cells(rows.count,1).end(xlup).resize(4).entirerow.insert
next
next
End Sub

[SamT]

Sub Insert_Rows()
Dim WkBk As Workbook
Dim WkSht As Worksheet
Application.ScreenUpdating = False
For Each WkBk In Workbooks
For Each WkSht In WkBk.Worksheets
WkSht.Cells(Rows.Count, "A").End(xlUp).Offset(-1).Resize(4).EntireRow.Insert
Next
Next
Application.ScreenUpdating = True
End Sub


Range.Offset(NumRows, Num Cols) 'Negative numbers for up and left
Range.Resize(NumRows, Num Cols) 'Negative numbers to shrink or subtract

[4hoteliers]

- Welcome !
- Please use code tags
-'inserting rows' isn't a clever thing to do in VBA: a worksheet always contains the same amount of rows/columns: rows.count

You could use:


Sub M_snb()
for j=1 to 3
for each sh in workbooks(j).sheets
sh.cells(rows.count,1).end(xlup).resize(4).entirerow.insert
next
next
End Sub

[4hoteliers]

I have tried the via of SamT but it does;t work...comes out syntax error...may i should add also the Range.Offset & Range.Resize you mention, but where should i put these two lines of code


- Welcome !
- Please use code tags
-'inserting rows' isn't a clever thing to do in VBA: a worksheet always contains the same amount of rows/columns: rows.count

You could use:


Sub M_snb()
for j=1 to 3
for each sh in workbooks(j).sheets
sh.cells(rows.count,1).end(xlup).resize(4).entirerow.insert
next
next
End Sub

[4hoteliers]

Mark007, thank you for the code, i tried it and works fine.
Thank you
Michalis

[SamT]

I had a typo in that code. it is fixed now.

Mark007 wrote the PHP server code that formats text when you use CODE Tags.

Click the # Icon to insert CODE Tags in your post

[4hoteliers]

I have tried it again and comes out the following error:

Run-time error '9':
Subscript out of range

will you please check and met m know.

Thank you very much
Michalis


- Welcome !
- Please use code tags
-'inserting rows' isn't a clever thing to do in VBA: a worksheet always contains the same amount of rows/columns: rows.count

You could use:


Sub M_snb()
for j=1 to 3
for each sh in workbooks(j).sheets
sh.cells(rows.count,1).end(xlup).resize(4).entirerow.insert
next
next
End Sub

[4hoteliers]

Can't find the new code.
will you please send me again the correct code
Than you
Michalis


I had a typo in that code. it is fixed now.

Mark007 wrote the PHP server code that formats text when you use CODE Tags.

Click the # Icon to insert CODE Tags in your post

[SamT]

#3 (http://www.vbaexpress.com/forum/showthread.php?55063-via-code-to-use-to-6-8-12-sheets&p=337859&viewfull=1#post337859)

[snb]

Please do not quote !

[4hoteliers]

I have tried again and comes out the error
Run-time error '104':
Application-defined or object-defined-error

Could you please help me on this
Thank you in advance
Michalis

[snb]

http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764574124.html

[SamT]

@ snb, I wonder who 4Hoteliers is talking to.

I wonder what his code is.

I wonder why he won't share any information at all about the situation with us.

[snb]

Probably talking to Mark007

[4hoteliers]

Dear SamT & snb

Sorry for the confusion

#3 code SamT has sent me, when i run it i get the error
Run-time error '1004':
Application-defined or object-defined-error

#2 code sub has sent me, when run, i get the error
Run-time error '9':
Subscript out of range

Could you please check and help me on this

Thank you both, in advance for your help
Michalis

[SamT]

What does this code give you? I removed a "Feature/Bug" from the code above

Sub VBAX_SamT_Insert_Rows()
Dim WkBk As Workbook
Dim WkSht As Worksheet
Application.ScreenUpdating = False
For Each WkBk In Workbooks
For Each WkSht In WkBk.Worksheets
WkSht.Cells(Rows.Count, "A").End(xlUp).Resize(4).EntireRow.Insert
Next
Next
Application.ScreenUpdating = True
End Sub
I bet that you were trying to test the original on an empty sheet.

[4hoteliers]

SamT, #17 works fine now.
I made a copy of the original worksheet, have tested it and works ok now.
the only problem i have now is that the via code cannot be saved in my personal macro workbook.
i have inserted a new module, copied your code, run it, have got the 4 lines inserted but cannot save it.
i work on a mac, with excel 2011 on it.
do you think that this version of excel creates the problem?

[SamT]

I use a PC running Windows XP, but no, the problem is with the Personal.xlsm Workbook.

Make sure the you only have once instance of Excel running when you try to save Personal.xlsm,

I never use Personal.xlsm for any code used in other Workbooks, except a couple that I use when working on someone else's Workbook ,like ShowAllSheets, SetZoom75, and SetZoom100. Other than those three, the only code in my version of Personal.xls is code that I often paste into other Workbooks for use in them.

[4hoteliers]

Thank SamT, managed to save it as per your instructions