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

 

[RAJESH1960]

Wait. I will record a video and will share it with you.

 

[johnnyL]

 

[RAJESH1960]

Sorry man. I am getting confused as my mind is else somewhere. Got to go now. Already received 2 calls from my office. Will share the correct data once I am back.

 

[RAJESH1960]

Link to Video of extracting Projected sales.
Video of Extract.mp4
Link to workbook which needs a little more of editing.
ClearData all in one button.xlsm
Clear data code to be edited if required as it is a recorded macro. There are no fixed ranges in this code else could have been changed. In the total code the range needs to be changed from 100000 to unlimited. And lastly combine all the codes and assign to one button.

 

[johnnyL]

View attachment 73783

Ok I fixed the error that I was getting

    ActiveWorkbook.Worksheets("Total").Sort.SortFields.Add2 Key:=Range( _
        "D2:D100000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal

I had to change the 'Add2' part of that line to just 'Add' to make it compatable with my version of excel.

So where are we at now? Making it run with one button? What order do you want the buttons to be ran in?

 

[RAJESH1960]

Extract >Shift amounts >post data to daily >Get total
If you can add the clear old data in the first line of Extract then I need not use 2 buttons.
In the extract sheet you have to delete the data from 7 onwards to end only. The rows from 1-6 in extract , as I noticed are automatically rewritten.

 

[RAJESH1960]

Were you able to edit the code regarding the CR sheet. If CR cell A2 is nullstring then post only Daily entries to Total else combine both entries....

 

[johnnyL]

So you want the Clear old data to always run first?

 

[RAJESH1960]

So you want the Clear old data to always run first?

Yes

 

[johnnyL]

Were you able to edit the code regarding the CR sheet. If CR cell A2 is nullstring then post only Daily entries to Total else combine both entries....

Haven't looked at that yet.