Auto-fill items repeatedly for two sheets based on another
Solved
leapout
TrowaD
- Posts
- 20
- Registration date
- Monday March 1, 2021
- Status
- Member
- Last seen
- April 26, 2022
TrowaD
- Posts
- 2880
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- May 2, 2022
Related:
- Auto-fill items repeatedly for two sheets based on another
- Auto copy data from sheet1 to sheet based on criteria ✓ - Forum - Excel
- Auto Transfer of a filtered item to a different sheet. ✓ - Forum - Excel
- Excel macro to create new sheet based on value in cells - Guide
- Auto notify of email when excel sheet updated ✓ - Forum - Excel
- Excel move row to another sheet based on cell value without macro ✓ - Forum - Excel
1 reply
TrowaD
Feb 22, 2022 at 12:08 PM
- Posts
- 2880
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- May 2, 2022
Feb 22, 2022 at 12:08 PM
Hi Leapout,
Check out the following code:
This is the code for FIL1. Change it on code line 5 for FIL2.
Best regards,
Trowa
Check out the following code:
Sub RunMe() Dim mVal1, mVal2 As String Dim lRow, x As Long Sheets("FIL1").Select With Sheets("REP") lRow = .Range("B1").End(xlDown).Row For Each cell In Range("B2:B" & Range("B1").End(xlDown).Row) mVal1 = Replace(cell.Value, " ", vbNullString) x = 1 Do x = x + 1 mVal2 = Replace(.Cells(x, "B") & .Cells(x, "C") & .Cells(x, "D"), " ", vbNullString) If mVal1 = mVal2 Then If .Cells(x, "A").Value <> vbNullString Then cell.Offset(0, -1).Value = .Cells(x, "A").Value Else cell.Offset(0, -1).Value = .Cells(x, "A").End(xlUp).Value End If End If Loop Until x = lRow Next cell End With End Sub
This is the code for FIL1. Change it on code line 5 for FIL2.
Best regards,
Trowa
Updated on Feb 22, 2022 at 01:59 PM
Impressive! Actually I got idea from some forum to implement your code for two sheets by putting in array.
So see bold some adjusting how your code becomes.
many thanks for your great work
Feb 24, 2022 at 11:46 AM