• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Why is the macro not returning the layout?

Why isn't the macro returning the layout? Whenever I hit the command button, it does nothing and only displays "layout inserted after every 102 rows successfully

Code:
Sub Rows()
    Dim ws As Worksheet
    Dim layoutRange As Range
    Dim copyRange As Range
    Dim pasteRange As Range
    Dim lastRow As Long
    Dim nextRow As Long
    Dim i As Long
    
    Set ws = ThisWorkbook.Sheets("RAW")
    
    Set layoutRange = ws.Range("A9:N109")
    
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    nextRow = 110
    Do While nextRow < lastRow
        Set copyRange = layoutRange
        Set pasteRange = ws.Rows(nextRow).Resize(copyRange.Rows.Count, copyRange.Columns.Count)
        
        copyRange.Copy
        pasteRange.PasteSpecial Paste:=xlPasteFormats
        pasteRange.PasteSpecial Paste:=xlPasteValues
        pasteRange.PasteSpecial Paste:=xlPasteValidation
        pasteRange.PasteSpecial Paste:=xlPasteFormulas
        
        Application.CutCopyMode = False
        
        For Each cell In pasteRange
            If cell.HasFormula Then
                cell.Formula = Replace(cell.Formula, "$", "")
                cell.Formula = Application.ConvertFormula(cell.Formula, xlA1, xlA1, , pasteRange.Address)
            End If
        Next cell
        
        nextRow = nextRow + 102
    Loop
    
    MsgBox "Layout inserted after every 102 rows successfully!"
End Sub
 
There has to be a last row, unless there is no data in that column in which case that’s why the code does nothing.
 
Back
Top