Inserting 200 rows in 50 worksheets

OfficeHelp123

New Member
Joined
Feb 17, 2016
Messages
2
Hi,
I need to insert about 200 rows at three different places in each worksheet of an excel file that contains roughly 50 worksheets. The inserted rows should be in a certain format, like the following:

28mnvhc.png


So what I need to do is to insert around 200 copies of row 74 at three places in all worksheets, for example below row 74, then below row 279 and then below row 484.
If I do this for more than 6 worksheets grouped together, excel will give the following error message:

2pplh6h.png


When I select all worksheets together, I can only insert a maximum of 15 rows without crashing excel.

Since I have to repeat this for about 20 similar excel files each containing as well around 50 worksheets, both of the procedures mentioned above will take way too much time. Is there an easier way to accomplish this (maybe with a macro in VBA)?

Thank you very much!
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Maybe you can try this macro.
The steps:
Run macro Try1
Do your 'copy insert paste' to some grouped sheets.
Run macro Try2

Code:
Sub Try1()
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
End Sub


Sub try2()
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

End Sub
 
Upvote 0
Hi,

thank you for the quick reply! I tried the macros as you described, but when I insert too many rows, Excel will still return that there is "not enough memory to complete this action" (see error message from original post). Could I try something else? Or is it just "too much" for a 32-bit version of Excel?
 
Upvote 0
Code:
Sub CopyRows() Dim xSheets As Variant
 xSheets = Array("Foglio1", "Foglio2", "Foglio3", "Foglio4")
 For x1 = 0 To UBound(xSheets)
  xS = xSheets(x1)
  With Sheets(xS)
  'rows to be copied
   .Rows("74").Copy
  'lines to paste (move the rest down)
   .Rows("75:275").Insert Shift:=xlDown
   .Rows("279:479").Insert Shift:=xlDown
   .Rows("484:684").Insert Shift:=xlDown
  End With
 Next x1
End Sub

try like this
 
Upvote 0

Forum statistics

Threads
1,217,356
Messages
6,136,077
Members
449,988
Latest member
Mabbas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top