VBA to copy and paste in batches

Hi All,

I think this should be simple for some guys here, but I am relatively new to VBA, and so am no good at starting code from scratch.

I have 2 files (source and dashboard). In my Source file I have a "Data for pasting" sheet with over 45000 rows and 70 columns; and I need to copy the content of this sheet into my dashboad file in a sheet called "Iss Data Paste".

The issue here is that in my dashboard file I have 7 sheets full of formulas that calculate from the "Iss Data Paste" sheet. And when I do the copy-paste values excel cannot refresh all the sheets in the file dashboard and it returns "#N/A" everywhere. I tried with calculation option manual, but still when I hit F9 i get the errors. Also I tried the copy sheet and it did not work.

I think, the only option left here is to have a VBA code that will copy the rows 100 at the time from source to dashboard and give the dashboard file to calculate before pasting another batch. In sumary the code needs to do this:

1- Copy line 1 to 100 from the "data for pasting" sheet sheet "source" file
2- Paste values in the "iss Data Paste" Sheet in the Dashboad file
3- Calculate all formulas in the dashboad file
4- Check that the next batch has some data
5- Copy line 101 to 200 from the "data for pasting" sheet sheet "source" file
6- Paste values in the "iss Data Paste" Sheet in the Dashboad file
7- Calculate all formulas in the dashboad file
8 repeat copy, paste, and calculate steps until if finds that the next batch is only blanck cells

To bad I dont know how to write the code. I will appreciate your help on this code.

Oscar

Hi Tony,

The #N/A, in this case, is because it cannot calculate. I am sure of that. And I get the errors: "Excel ran out of resources while attempting to calculate one or more formulas. As a result, thses formulas cannot be evaluated" followed by the error "Not enough resources to display completely"
The data structure, names, and everything else is the same, but the data is so massive that Excel breaks when calculating, leaving me with only #N/A. I checked my formulas and they are correct.

Thanks

Tony, I wish this would be the same case; but it is not. I already optimized the formulas. Actually, this is a template someone else created and I was able to decrease it size by 25%, and removed non necessary formulas. Said this, I think I have to live with it and make it a case without solution. Just as an FYI I have 51,500 formulas in the book.
Thanks for your replies, still I think that the macro top copy portions of the databroken by batches of 50 will help, can you help me with that code?
Thanks in advance