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]

Which error 1004 are you getting at that line you posted? What does the rest of the message say?

 

[RAJESH1960]

Hi JohnnyL. Give me some time, I will share with you a new workbook and explain where I am stuck at.

 

[RAJESH1960]

Hello JohnnyL

I have created 3 different modules to get the extract of Projected sales for a single month. The 4th and final module still working on it Once all the problems are solved you can help me to connect all the modules to a single button.

If you press the first button “Extract Day Wise”, the Extract sheet is filled with random figures to match the daily projected sale amount displayed in row 3. Working perfect.

If you press the second button “Shift Amount”, 2 amounts from the last value in columns N to AB are cut and pasted in the last empty cell in Columns A to M. Here the code is not working properly. Also problem in where there is data in more than 2000 rows in some columns, are left blank. I need your help to correct this issue first.

If you press the third button “Post Data to Daily”, all the amounts from the Extract sheet are displayed in a columnar format with date. As there are blank rows in the Extract Day wise sheet, here too the rows are displaying blank in the amount column. Once the shift amount code is corrected then this will work perfectly.

to edit Projected Sales.xlsm

 

[RAJESH1960]

The code is written in such a way that when you press the First button, in the extract sheet, the sum of A2: AE2 willbe exactly same as the sum of A3:AE3
and after you press the second button, the sum of A2: AE2 will be exactly same as the sum of A3:AE4.
I have never come across such style of writing the code. I will not be able to re create anything from this as there are no comments after each line.

 

[RAJESH1960]

This is to inform the forum that I have shared this part of the code yesterday in another forum.
Need help to change the range in the code

 

[johnnyL]

If you press the second button “Shift Amount”, 2 amounts from the last value in columns N to AB are cut and pasted in the last empty cell in Columns A to M. Here the code is not working properly.

That is exactly what the code does. Are you saying that it is not supposed to copy the last 2 values in a column, write those two values to the end of another column & then delete the 2 values that were copied?

 

[RAJESH1960]

That is exactly what the code does. Are you saying that it is not supposed to copy the last 2 values in a column, write those two values to the end of another column & then delete the 2 values that were copied?

It is cutting the values of rows 1999 and 2000 on the 17th and 24th of that month (Sunday) as the rows with values crosses 2000 rows. Whereas it has to cut the rows 2132 and 2133. Earlier, even I didn't notice that but when I entered a larger figure to get the projected sales I noticed that the Shift value code is taking only 2000 rows into consideration.

 

[johnnyL]

I will take that as a partial answer to my question.

Try the following, Pay attention to the line that is commented with '???' correct that line if needed:

Option Explicit
'Solved by Fluff
Sub ShiftAmounts()
'
    Dim LastRowInRange  As Long
    Dim rng             As Range
'
    Sheets("Extract").Select
'
    LastRowInRange = Range("O:AE").Find("*", , xlFormulas, , xlByRows, xlPrevious).Row                                  ' Find last used row in the specified column range
'
'below line replace 2000 and Change the range to O2:AB2000 to end
    For Each rng In Range("O2:AB" & LastRowInRange).Columns                                                             ' ??? Data goes to column AE ??? should the AB be changed to AE?
        With rng.SpecialCells(xlConstants)
''            Cells(2000, .Offset(, -14).Column).End(xlUp).Offset(1).Resize(2).Value = .Offset(.Count - 2).Resize(2).Value
            Cells(Cells(Rows.Count, rng.Column - 14).End(xlUp).Row, .Offset(, -14).Column).Offset(1).Resize(2).Value = _
                    .Offset(.Count - 2).Resize(2).Value                                                                 '       Move last 2 values from one column to end of another column
            .Offset(.Count - 2).Resize(2).Value = ""
        End With
    Next rng
End Sub

 

[RAJESH1960]

The shift amounts code is working perfectly now. It corrected the Daily sheet too.