I'm bringing data in from a download and it appears in reverse row order to what I need. This seems like a very common situation so I was trying to either find or write a subroutine for the purpose of reversing the row order.
So, the "specification" could be: copy a range in one sheet and paste it in another sheet with the row order reversed.
One can meet this need by creating a sorting macro but the strangeness here seems good to be investigated.
Here is the code with the short loop near the end:
''
'' Copy TempData to Data - Reversing dates
''
Dim SourceSheet As Worksheet
Dim DestinationSheet As Worksheet
Dim SourceRange As Range
Dim DestRow As Long
Dim SourceRow As Long
Dim lastrow As Long
Dim i As Long
' Set references to the source and destination sheets
Set SourceSheet = ThisWorkbook.Sheets("TempData")
Set DestinationSheet = ThisWorkbook.Sheets("Data")
' Find the last used row in the source sheet
lastrow = SourceSheet.Cells(SourceSheet.Rows.Count, 1).End(xlUp).Row
' Set the source range
Set SourceRange = SourceSheet.Range("A2:M" & lastrow)
DestRow = 2
' Copy and paste the rows in reverse order
For i = lastrow To 1 Step -1
Debug.Print "PreCopy i and DestRow "; i; DestRow
SourceRange.Rows(i).Copy Destination:=DestinationSheet.Cells(DestRow, 1)
Debug.Print "PostCopy i and DestRow "; i; DestRow
'Application.Wait currentTime + TimeSerial(0, 0, 5)
DestRow = DestRow + 1
Debug.Print "DR was incremented i and DestRow "; i; DestRow
Next i
The code is intended to take the last row of the original data and put it in the first row of the destination and iterate from there.
Instead, it takes the last row of the original data and puts it in the 2nd row of the destination - skipping the first row of the destination altogether.
After that, the iteration continues as it should but ends one row too many in the destination as one might expect if the first action is delayed.
The Debug.Print statements in the loop are helpful. I watch the destination sheet in a separate screen to confirm all this happening.
Changing DestRow value to 1 seems to fix it but I don't know why. Seems a good opportunity for learning.
Thank you!!
Bookmarks