Option Explicit
Sub Q_28727859()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim rngSrc As Range
Dim rngTgt As Range
Set sh1 = Sheets("Title_Frame_Register")
Set sh2 = Sheets("Revisions")
Set rngTgt = sh2.Range("A4")
Set rngSrc = sh1.Range("B5")
Set rngSrc = sh1.Range(rngSrc, sh1.Cells(sh1.Rows.Count, 2).End(xlUp))
rngSrc.Cells.SpecialCells(xlCellTypeConstants).Copy rngTgt
End Sub
Please note that none of your modules requires explicit variable declaration. You should add an Option Explicit statement to all of your modules and compile your VBProject.
If the columns have data in them1. Is that for the AU:AX block or on a row-by-row basis?
Sub Q_28727859()
Dim wksSrc As Worksheet, wksTgt As Worksheet
Dim rng As Range
Dim rngSrc As Range
Dim rngTgt As Range
Const cMinRevCol As Long = 47
Set wksTgt = Sheets("Title_Frame_Register")
Set wksSrc = Sheets("Revisions")
Application.ScreenUpdating = False
Set rngSrc = wksSrc.Range("B5")
Set rngSrc = wksSrc.Range(rngSrc, wksSrc.Cells(wksSrc.Rows.Count, 2).End(xlUp))
Set rngSrc = rngSrc.Cells.SpecialCells(xlCellTypeConstants)
For Each rng In rngSrc
Set rngTgt = wksTgt.Cells(rng.Row, wksTgt.Columns.Count).End(xlToLeft)
If rngTgt.Column < cMinRevCol Then
Set rngTgt = wksTgt.Cells(rng.Row, cMinRevCol)
Else
Set rngTgt = rngTgt.Offset(0, 4)
End If
wksTgt.Range(rngTgt, rngTgt.Offset(0, 2)).Value = wksSrc.Range(rng, rng.Offset(0, 2)).Value
Next
Application.ScreenUpdating = True
End Sub
Set rngTgt = rngTgt.Offset(0, 4 + (5 - wksTgt.Range(rngTgt, rngTgt.Offset(0, 4)).Cells.SpecialCells(xlCellTypeVisible).Count))
Sub Q_28727859()
Dim wksSrc As Worksheet, wksTgt As Worksheet
Dim rng As Range
Dim rngSrc As Range
Dim rngTgt As Range
Const cMinRevCol As Long = 47
Set wksTgt = Sheets("Title_Frame_Register")
Set wksSrc = Sheets("Revisions")
Application.ScreenUpdating = False
Set rngSrc = wksSrc.Range("B5")
Set rngSrc = wksSrc.Range(rngSrc, wksSrc.Cells(wksSrc.Rows.Count, 2).End(xlUp))
Set rngSrc = rngSrc.Cells.SpecialCells(xlCellTypeConstants)
For Each rng In rngSrc
Set rngTgt = wksTgt.Cells(rng.Row, wksTgt.Columns.Count).End(xlToLeft)
If rngTgt.Column < cMinRevCol Then
Set rngTgt = wksTgt.Cells(rng.Row, cMinRevCol)
Else
'nudge rngTgt to the left until we are at a Revision Date column
Do Until wksTgt.Cells(3, rngTgt.Column).Value = "Revision Date"
Set rngTgt = rngTgt.Offset(0, -1)
Loop
Set rngTgt = rngTgt.Offset(0, 4 + (5 - wksTgt.Range(rngTgt, rngTgt.Offset(0, 4)).Cells.SpecialCells(xlCellTypeVisible).Count))
End If
wksTgt.Range(rngTgt, rngTgt.Offset(0, 2)).Value = wksSrc.Range(rng, rng.Offset(0, 2)).Value
Next
Application.ScreenUpdating = True
End Sub
Sub Clear()
ActiveSheet.Range(ActiveSheet.Range("A3"), ActiveSheet.UsedRange.Cells.SpecialCells(xlCellTypeLastCell)).ClearContents
End Sub
Saurabh...