How do I change the range in this code.

[RAJESH1960]

'for' Loop @Fluff need your help to edit this..
This code was shared by Fluff last year which worked correctly in the previous project. At first the data extracted was filled in around 600 rows but when the value of the amount to be extracted has increased, it is not possible to get the data in 600 rows. Hence the formulas in rows after the 600th row have been shifted to the top so that I can get unlimited rows to fill the data.
If the number of rows is avoided and changed to unlimited rows it will be really helpful in future.
I am trying this code by changing the range in the code by editing it. But I am getting a run time error 1004 at this line. Can someone please help me to correct it.? I want to add this code at the end of the existing code.

         Cells(2000, .Offset(, -14).Column).End(xlUp).Offset(1).Resize(2).Value = .Offset(.Count - 2).Resize(2).Value

Link to the file is below
Shift amounts.xlsm

 

[johnnyL]

Ok, give me a few to look at your most recent file. Your most most recent file works right?

 

[johnnyL]

I guess not.

The 'Get Total' button produces an error.

 

[RAJESH1960]

I forgot to include the clear the Total sheet data in the Clear old Data macro.

Sheets("Total").Select
    Range("A2:E2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range("A2").Select

One more chance of error. The CR sheet may or may not have data at times. So when the code is combining the data of Daily and CR you need to add an if condition. If there is data in CR sheet then combine else display only the data in Daily sheet.
I know it is past your bed time. Let us continue this tonight. I too have got to go to office now. See you tonight.

 

[RAJESH1960]

The 'Get Total' button produces an error.

Re assign the macro by right clicking on the button.

 

[johnnyL]

Still get error.

Please make sure your code works before you submit it for further corrections.

 

[RAJESH1960]

This workbook includes the clear data for total sheet and all the buttons are assigned with their respective macros.
New ClearData all in one button.xlsm

 

[RAJESH1960]

Once you have used the clear old data, you will have to fill data in the CR sheet manually, at least one entry for the code to work correctly the next time else it will display everything wrong. Check the difference in cell G2 of the total sheet. If it is a small amount then it has worked correctly. That is the only way to check if the data is extracted successfully.

 

[johnnyL]

Like I said, when you get a testable script, let us know.

 

[RAJESH1960]

Each time you use the clear button, just add 2 lines in the CR sheet like this. The code will display the result correctly. That is why I told you to add an if statement in the code. If CR cell A2 is blank then display Daily sheet data only in the total sheet else combine both the data.

 

[johnnyL]

Still get the same error in the sort section of your code.