Copy a predefined cell range put in a column to a another column with looping

[nbuddhi]

Dear team,

Column AD3:AD107 have the formula to identify the copy range; row 3 to 107 will be filed as per the data availability. I need to check the each rows AD3:AD107 and grab the available cell range and copy those range into column AF starting from row 3. AS per below AD3 range is FD3:FD35, but it may varied with the calculation, once AD3 range copy starting from AF3 and next row data (FE3:FE35, as per current calculation) range has to copied to next available row of AF column and this need to repeat till find the no valid cell range mentioned in AD3:AD107 . Thanks.

FD3:FD35

FE3:FE35

FF3:FF35

FG3:FG35

FH3:FH35

FI3:FI35

FJ3:FJ35

FK3:FK53

FL3:FL35

FM3:FM53

FN3:FN53

FO3:FO77

FP3:FP53

 

[Trebor76]

Hi nbuddhi,

Try this:

Option Explicit
Sub Macro1()

    Dim rngCell As Range
    Dim lngPasteRow As Long
    
    Application.ScreenUpdating = False
    
    For Each rngCell In Range("AD3", Range("AD" & Rows.Count).End(xlUp))
        lngPasteRow = IIf(lngPasteRow = 0, 3, Cells(Rows.Count, "AF").End(xlUp).Row + 1)
        Range(CStr(rngCell.Value)).Copy Destination:=Range("AF" & lngPasteRow)
    Next rngCell
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[nbuddhi]

Hi nbuddhi,

Try this:

Option Explicit
Sub Macro1()

    Dim rngCell As Range
    Dim lngPasteRow As Long
   
    Application.ScreenUpdating = False
   
    For Each rngCell In Range("AD3", Range("AD" & Rows.Count).End(xlUp))
        lngPasteRow = IIf(lngPasteRow = 0, 3, Cells(Rows.Count, "AF").End(xlUp).Row + 1)
        Range(CStr(rngCell.Value)).Copy Destination:=Range("AF" & lngPasteRow)
    Next rngCell
   
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

Dear Robert,

Thank you for your support. Sorry, I missed to mentioned that I need to copy indirect cell values instead formula into AF area as indirect cell ranges mentioned on AD area having formulated data. Now ranges are perfectly copy with formula, kindly help me to paste them as values only. Thank you.

Thanks & Best Regards,
nbuddhi

 

[Trebor76]

kindly help me to paste them as values only

Try this:

Option Explicit
Sub Macro2()

    Dim rngCell As Range
    Dim lngPasteRow As Long
    
    Application.ScreenUpdating = False
    
    For Each rngCell In Range("AD3", Range("AD" & Rows.Count).End(xlUp))
        lngPasteRow = IIf(lngPasteRow = 0, 3, Cells(Rows.Count, "AF").End(xlUp).Row + 1)
        Range(CStr(rngCell.Value)).Copy
        Range("AF" & lngPasteRow).PasteSpecial xlPasteValues
    Next rngCell
    
    Application.ScreenUpdating = True

End Sub

 

[nbuddhi]

Try this:

Option Explicit
Sub Macro2()

    Dim rngCell As Range
    Dim lngPasteRow As Long
   
    Application.ScreenUpdating = False
   
    For Each rngCell In Range("AD3", Range("AD" & Rows.Count).End(xlUp))
        lngPasteRow = IIf(lngPasteRow = 0, 3, Cells(Rows.Count, "AF").End(xlUp).Row + 1)
        Range(CStr(rngCell.Value)).Copy
        Range("AF" & lngPasteRow).PasteSpecial xlPasteValues
    Next rngCell
   
    Application.ScreenUpdating = True

End Sub

Dear Robert,

Many Thanks. It worked perfectly.

Thanks & Best Rgds,
nbuddhi

 

[Trebor76]

Thanks for letting us know and you're welcome