NorthernHemishpere
New Member
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