Pull data from columns in sheet A based on the value in column L and insert into another sheet

ChrisMI22 

To automate this process in Excel, you can use a VBA (Visual Basic for Applications) macro. Here's a step-by-step guide to how you can accomplish this task:

Steps to Create a VBA Macro:

1. Open Excel and press ALT + F11 to open the VBA Editor.
2. In the editor, click Insert -> Module to add a new module.
3. Copy and paste the following VBA code into the module:

VBA Code:

Vba Code is untested backup your file.

Sub MoveDataBasedOnGrantNumber()
    Dim wsPersonnel As Worksheet
    Dim wsTarget As Worksheet
    Dim lastRowPersonnel As Long
    Dim targetSheetName As String
    Dim nextEmptyRow As Long
    Dim i As Long
    
    ' Set the "Personnel" worksheet
    Set wsPersonnel = ThisWorkbook.Sheets("Personnel")
    
    ' Find the last row in the "Personnel" sheet
    lastRowPersonnel = wsPersonnel.Cells(wsPersonnel.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through each row in the "Personnel" sheet
    For i = 2 To lastRowPersonnel ' Assuming row 1 is a header
        ' Get the grant number (value in column L)
        targetSheetName = wsPersonnel.Cells(i, "L").Value
        
        ' Check if a worksheet with the name matching the grant number exists
        On Error Resume Next
        Set wsTarget = ThisWorkbook.Sheets(targetSheetName)
        On Error GoTo 0
        
        If Not wsTarget Is Nothing Then
            ' Find the next empty row in the target sheet (in column H)
            nextEmptyRow = wsTarget.Cells(wsTarget.Rows.Count, "H").End(xlUp).Row + 1
            
            ' Copy the value from column K (salary) in the Personnel sheet to column H in the target sheet
            wsTarget.Cells(nextEmptyRow, "H").Value = wsPersonnel.Cells(i, "K").Value
            
            ' Copy the value from column A (name) in the Personnel sheet to column D in the target sheet
            wsTarget.Cells(nextEmptyRow, "D").Value = wsPersonnel.Cells(i, "A").Value
        End If
        
        ' Clear the wsTarget for the next loop
        Set wsTarget = Nothing
    Next i

    MsgBox "Data transferred successfully!"
End Sub

Explanation:

1. wsPersonnel: Refers to the "Personnel" sheet.
2. Loop through each row in the "Personnel" sheet to get the grant number (from column L) and check if a sheet with that name exists.
3. If the target sheet exists, it finds the next empty row in column H and places the value from column K (salary) of the "Personnel" sheet into column H of the target sheet.
4. The value from column A (name) of the "Personnel" sheet is placed in column D of the target sheet in the same row.

Running the Macro:

1. Press ALT + F8, choose the macro MoveDataBasedOnGrantNumber, and click Run.

Notes:

• Ensure that the names in column L exactly match the sheet names.
• Make sure the destination sheets (grant numbers) already exist.
• Adjust the column letters and ranges if necessary, depending on your actual data.

This macro will iterate through all rows in "Personnel" and transfer data to the respective grant number sheets.

Alternative Solution proposals:

You can use alternative solutions, like Using FILTER Formulas.

Example: In the sheet named 101242, use this formula to pull the corresponding data from the Personnel sheet:

For Column D (names):

=FILTER(Personnel!A:A, Personnel!L:L = "101242")

For Column H (salaries):

=FILTER(Personnel!K:K, Personnel!L:L = "101242")

This will display all names from column A and all salaries from column K in the rows where column L has the grant number 101242.

The formulas using FILTER can dynamically pull data when changes are made, but they require newer Excel versions (2019 and Microsoft 365).

Pivot Tables: Pivot tables are more useful for reporting, but you would still need to manually paste data into the right sheets. The text, steps and VBA Code was created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.