modify VBA Code to shift older data down and copy newer at top

[David gonzalez]

Hi,

on daily basis and with aid of excel helper info I create a report (between 9 to 24 rows).
So i have a vba code that copy this Info and paste the data in next blank cell in Report area located in same sheet (but different area).
The code is working fine, except that always newer data it copy at bottom of the sheet and data is getting bigger.

Then what i need to accomplish is to modify the line of code and change the Order of copy
From Top newer (today data) and move/shift the Older (yesterday data) to Bottom.

This the actual data (see date Oct 5, oct 6, oct 7 down)

This Is the Ideal Data with newest date at Top ( Oct 7, Oct 6, Oct 5..)

This is the actual code

Sub ReporTrack_Test()    Dim j As Long
    For j = 4 To 12 
        If Cells(j, 58).Value > 1 And Cells(j, 58).Value < 36 Then _
        Cells(Rows.count, 20).End(xlUp).Offset(1).Resize(, 22).Value = Cells(j, 58).Resize(, 22).Value
    
    Next j
End Sub

Thank you !


Best Regards

David

 

[JoeMo]

This will sort your data on the date column - will that work for you?

Sub ReporTrack_Test()
Dim j As Long
    For j = 4 To 12
        If Cells(j, 58).Value > 1 And Cells(j, 58).Value < 36 Then _
        Cells(Rows.Count, 20).End(xlUp).Offset(1).Resize(, 22).Value = Cells(j, 58).Resize(, 22).Value
    
    Next j
    With Range("AG2")
        .Sort key1:=[AG3], order1:=xlDescending, Header:=xlYes
    End With
End Sub

 

[David gonzalez]

Hi mr joemo,

It copy first 3 line of report, but it pop a run-time error 1004, "This operation requires the merged cell to be identically sized",
and the debug highlight this line:

 .Sort key1:=[AG3], order1:=xlDescending, Header:=xlYes

Best Regards

David

 

[David gonzalez]

Hi Mr Joemo,

Is strange, the excel helper source and Report, i select the whole range and make sure that is not data merged,
and still the same error. hope to find the spot, Still checking...


Thank you



Best Regards

David

 

[David gonzalez]

Hi !

i check cell by cell of report and Source area (excel helper),
-.right clicked a large range from top to bottom and clear if merged cell, so Un merged (did it several times)
-.Same whole area Clear if Conditional Formatting present (just in case)
-.Set the report and Source area (excel helper) with exact same fonts and size, cells size.

and still no clue.. might i'm missing something (i don't know) Any ideas what else to check?



Best Regards

David

 

[JoeMo]

Hi !

i check cell by cell of report and Source area (excel helper),
-.right clicked a large range from top to bottom and clear if merged cell, so Un merged (did it several times)
-.Same whole area Clear if Conditional Formatting present (just in case)
-.Set the report and Source area (excel helper) with exact same fonts and size, cells size.

and still no clue.. might i'm missing something (i don't know) Any ideas what else to check?



Best Regards

David

To clarify you are still getting this error: run-time error 1004, "This operation requires the merged cell to be identically sized"?

If yes, are cells in row 1 that contain REPORT merged?

Is there data beyond the images you posted in your initial post?

 

[David gonzalez]

Hi Mr JoeMo.

Yes sir the same error, : run-time error 1004, "This operation requires the merged cell to be identically sized"?

The report start on row 3 (Col 20 to 40)

The Excel Helper data start on Row 4 (Col 51 to 78 )

on both area including Above and bellow (just in case) i clean if Conditional formatting exist,Clean and set if cell merged, Including wrap text
set the fonts with same size, also i set the cell height and width.
But Might be i'm missing something.

P.S
i been checking for info, incluidng your post
http://www.mrexcel.com/forum/excel-questions/762499-run-time-error-1004-a.html



Thank you !


Best Regards

David

 

[JoeMo]

In AE1:AL1 you have the word Report. Are those cells merged?

 

[David gonzalez]

Hi Mr JoeMo.
Initially with the picture it was the only cell merged.
but at soon i replaced the old for the new module and received the Error, i changed and check the whole thing.


Best Regards

David

 

[JoeMo]

Hi Mr JoeMo.
Initially with the picture it was the only cell merged.
but at soon i replaced the old for the new module and received the Error, i changed and check the whole thing.


Best Regards

David

Hard to diagnose your problem without seeing your sheet. If you want me to look further, PM me and I will give you an email address to send me your workbook.