Forum Discussion
ChrisMI22
Sep 13, 2024Copper Contributor
Pull data from columns in sheet A based on the value in column L and insert into another sheet
I have a sheet called 'Personnel' from which I would like to take values from different columns and insert it into specific fields of the next blank row of the sheet whose name matches the value in column L of Personnel.
In the sheet named 'Personnel' Column L has a drop down of values corresponding to grant numbers. If the value is grant number '101242', take the data in column K and insert it into the next blank row of column H of the sheet named 101242. Then also take the value of column A from the same row in 'Personnel' and insert it into the same row as previously into column D of the sheet '101242'. I would like this to occur for each value in column L of Personnel to insert the name and salary into the corresponding Expense sheet for that grant.
- NikolinoDEGold Contributor
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:
- Open Excel and press ALT + F11 to open the VBA Editor.
- In the editor, click Insert -> Module to add a new module.
- 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:
- wsPersonnel: Refers to the "Personnel" sheet.
- 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.
- 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.
- 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:
- 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.