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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,791
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 ...
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,512
Messages
5,837,779
Members
430,516
Latest member
thaling

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