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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
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
 

OfficeHelp123

New Member
Joined
Feb 17, 2016
Messages
2
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?
 

makexcel

Board Regular
Joined
Jan 28, 2016
Messages
71
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
 

makexcel

Board Regular
Joined
Jan 28, 2016
Messages
71
OfficeHelp123 , you solved the problem ? if you are curious about how ...
 

Watch MrExcel Video

Forum statistics

Threads
1,130,443
Messages
5,642,159
Members
417,258
Latest member
amk1979

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
Top