I have 2 worksheets, if B2 in worksheet 1 matches with anything in column A in worksheet 2, I want to insert that row underneath the row in worksheet 1. and so on.
I have insert a demo with 3 worksheets.
Thanks
I have 2 worksheets, if B2 in worksheet 1 matches with anything in column A in worksheet 2, I want to insert that row underneath the row in worksheet 1. and so on.
I have insert a demo with 3 worksheets.
Thanks
![]()
Sub total() r = 2 Sheets(3).Range("a:d").ClearContents While Sheets(1).Cells(r, 1) <> "" For j = 1 To 3 Sheets(3).Cells(r, j) = Sheets(1).Cells(r, j) Next j r = r + 1 Wend r2 = 2 While Sheets(2).Cells(r2, 1) <> "" Set c = Sheets(3).Range("a:a").Find(Sheets(2).Cells(r2, 1), LookIn:=xlValues) If c Is Nothing Then rx = r r = r + 1 Else c3 = c.Row Sheets(3).Range(c.Row + 1 & ":" & c.Row + 1).Insert Shift:=xlShiftDown rx = c.Row + 1 End If For j = 1 To 3 Sheets(3).Cells(rx, j) = Sheets(2).Cells(r2, j) Next j r2 = r2 + 1 Wend End Sub
Try this, you would need to alter the "targetSheet"
Edit: By the way, this would append to the target... not clear it out first.
![]()
Option Explicit Sub copy_example() 'big ups to the general!' Dim targetSheet As Worksheet Set targetSheet = ThisWorkbook.Worksheets("generalDisarray") Call copySingleSource(ThisWorkbook.Worksheets("test1"), targetSheet, 1, 1, 3) Call copySingleSource(ThisWorkbook.Worksheets("test2"), targetSheet, 1, 1, 3) targetSheet.UsedRange.Sort Key1:=targetSheet.Cells(2, 1).Resize(targetSheet.Cells(2, 1).End(xlDown).Row - 1, 3), Order1:=xlAscending, Header:=xlYes End Sub Sub copySingleSource(ByRef sourceS As Worksheet, ByRef targetS As Worksheet, firstRow As Long, firstColumn As Long, tableWidth As Long) targetS.Cells(Rows.Count, firstColumn).End(xlUp).Offset(1, 0).Resize( _ sourceS.Cells(Rows.Count, firstColumn).End(xlUp).Row - 1, tableWidth).Value = _ sourceS.Cells(firstRow, firstColumn).Offset(1, 0).Resize( _ sourceS.Cells(Rows.Count, firstColumn).End(xlUp).Row - 1, tableWidth).Value End Sub
Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.
Please,mark your thread [SOLVED] if you received your answer.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks