Fill up non contiguous cells from contiguous cells/clipboard/range

[Bawltea]

My destination sheet contains Seat No. against which Roll No. (stored sequentially in another excel file) is to be filled up. Sample destination look like this:

The source file has Roll No in a sequential column and I have to copy/paste the Roll Nos. one column at a time for many rooms, for three to four session a day and sometimes for more than twenty day. Filling up the destination fill in just a single paste from Clipboard would really help and would be more helpful if automated.

 

[mumps]

What does the data in the source file look like?

 

[Bawltea]

What does the data in the source file look like?

A series of Roll No. in a column with corresponding names which is not needed for the destination file

 

[Bawltea]

A series of Roll No. in a column with corresponding names which is not needed for the destination file

Sl. No.	Roll No.	Name
1	ECO24-010	James Smith
2	ECO24-019	Nina Gomez
3	EDN24-021	Luna Lee
4	GEO24-032	Gina Lorenz
5	HIS24-044	Danny Kane
6	POL24-107	Harold Reed
7	POL24-108	Abel Humber
8	PSY24-112	John Ford
9	PUB24-115	Dana Lang

Like this

 

[mumps]

Are there 32 seat numbers with 32 corresponding Roll No.'s in the source file? Also, is the data in the source file in columns A, B and C? Does the data in the destination file start in column A?

 

[mumps]

Assuming that the data in the source file is in columns A, B and C and that the source file is already open, place this macro in a regular module in the destination workbook and run it from there. Change the sheet names (in red) and the workbook name (in blue) to suit your needs.

Sub FillRollNUm()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long, fnd As Range, desWS As Worksheet, srcWS As Worksheet
    Set desWS = ThisWorkbook.Sheets("Sheet1")
    Set srcWS = Workbooks("Source.xlsx").Sheets("Sheet1")
    v = srcWS.Range("A2", srcWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
    For i = LBound(v) To UBound(v)
        Set fnd = desWS.UsedRange.Cells.Find(v(i, 1), LookIn:=xlValues, lookat:=xlWhole)
        If Not fnd Is Nothing Then
            fnd.Offset(, 1) = v(i, 2)
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[Bawltea]

Are there 32 seat numbers with 32 corresponding Roll No.'s in the source file? Also, is the data in the source file in columns A, B and C? Does the data in the destination file start in column A?

The number of seats vary and also the setting i.e., number of rows and columns

 

[Bawltea]

Assuming that the data in the source file is in columns A, B and C and that the source file is already open, place this macro in a regular module in the destination workbook and run it from there. Change the sheet names (in red) and the workbook name (in blue) to suit your needs.

Sub FillRollNUm()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long, fnd As Range, desWS As Worksheet, srcWS As Worksheet
    Set desWS = ThisWorkbook.Sheets("Sheet1")
    Set srcWS = Workbooks("Source.xlsx").Sheets("Sheet1")
    v = srcWS.Range("A2", srcWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
    For i = LBound(v) To UBound(v)
        Set fnd = desWS.UsedRange.Cells.Find(v(i, 1), LookIn:=xlValues, lookat:=xlWhole)
        If Not fnd Is Nothing Then
            fnd.Offset(, 1) = v(i, 2)
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Thank you very much for your help. Now let me try. Will update ASAP. But it's getting late here, might not finish it tonight.