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

David gonzalez

New Member
Joined
Nov 19, 2013
Messages
16
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

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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,208
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
This will sort your data on the date column - will that work for you?
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
    With Range("AG2")
        .Sort key1:=[AG3], order1:=xlDescending, Header:=xlYes
    End With
End Sub
 

David gonzalez

New Member
Joined
Nov 19, 2013
Messages
16
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:

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


Best Regards

David
 

David gonzalez

New Member
Joined
Nov 19, 2013
Messages
16
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

New Member
Joined
Nov 19, 2013
Messages
16

ADVERTISEMENT

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

MrExcel MVP
Joined
May 26, 2009
Messages
17,208
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Nov 19, 2013
Messages
16

ADVERTISEMENT

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

MrExcel MVP
Joined
May 26, 2009
Messages
17,208
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
In AE1:AL1 you have the word Report. Are those cells merged?
 

David gonzalez

New Member
Joined
Nov 19, 2013
Messages
16
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,208
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,984
Messages
5,526,058
Members
409,682
Latest member
HisHailo

This Week's Hot Topics

Top