MthrOfDrgns
02-04-2022, 02:33 PM
Hi guys,
I am fairly new at this and was hoping to get some help.
I am doing a project in which data from a query is being pasted into a new sheet(Main Tab) and then being worked on by agents. After the data from that main sheet is resolved, there is a button for moving those rows into an another sheet(Completed)(The code for that part will be excluded because it works quite ok). Data that is being worked on on the main sheet sometimes cannot be done in one day, therefore, when pressing the "Get Report" button the day after, the user will only get new data from the query in the main sheet. There is a loop for finding matching issues, and if they are original(not found on main) they will be pasted on to the main sheet. But I don't know how to add a situation where, if there are matching issues both on the query and main sheet, to paste those rows to the main sheet, without putting them in the last row, rather taking their place(for ex. : If issue 4405 is both on Datatbl(query sheet) and Maintbl(main sheet). It is located in row 22(last row) on the query but in row 20 on main. Some of the data in columns of row 22 are updated in the query and need to take place on main in the row that was in (20) ). I hope I presented my problem as it is, and will give the code below.
Option Explicit
Public DataTbl, MainTbl, CompletedTbl As ListObject
Public MainLastRow As Long
Sub RefreshAndSearch(control As IRibbonControl)
'Search Data And Import Originals In Main / Variables
Set DataTbl = ThisWorkbook.Worksheets("PENDING Inbounds (RMA)").ListObjects("tbl_data")
Set MainTbl = ThisWorkbook.Worksheets("Main Tab").ListObjects("tbl_main")
Dim DataIssue, MainIssue As Range
Set DataIssue = DataTbl.ListColumns(1).DataBodyRange
Set MainIssue = MainTbl.ListColumns(1).DataBodyRange
Dim Issue As Range
Dim MainFound As Range
ThisWorkbook.Connections("Query - PENDING Inbounds (RMA)").Refresh
Application.CalculateUntilAsyncQueriesDone
'Loop For Searching Issues From Data In Main
For Each Issue In DataIssue
Set MainFound = MainIssue.Find(Issue)
If MainFound Is Nothing Then
If ThisWorkbook.Worksheets("Main Tab").Range("A2") = "" Then
DataTbl.ListRows(Issue.Row - 1).Range.Copy
MainLastRow = MainTbl.Range.Rows.Count
ThisWorkbook.Worksheets("Main Tab").Range("A" & MainLastRow).PasteSpecial Paste:=xlPasteValues
MainTbl.Range(MainLastRow, "AB") = Date
Else
DataTbl.ListRows(Issue.Row - 1).Range.Copy
MainLastRow = MainTbl.Range.Rows.Count
ThisWorkbook.Worksheets("Main Tab").Range("A" & MainLastRow + 1).PasteSpecial Paste:=xlPasteValues
MainTbl.Range(MainLastRow, "AB") = Date
End If
End If
Next Issue
ThisWorkbook.Worksheets("Main Tab").Range("A2").Select
'No New Issues Message
If Not MainFound Is Nothing Then
MsgBox "No new issues found.", , "Attention"
End If
End Sub
I am fairly new at this and was hoping to get some help.
I am doing a project in which data from a query is being pasted into a new sheet(Main Tab) and then being worked on by agents. After the data from that main sheet is resolved, there is a button for moving those rows into an another sheet(Completed)(The code for that part will be excluded because it works quite ok). Data that is being worked on on the main sheet sometimes cannot be done in one day, therefore, when pressing the "Get Report" button the day after, the user will only get new data from the query in the main sheet. There is a loop for finding matching issues, and if they are original(not found on main) they will be pasted on to the main sheet. But I don't know how to add a situation where, if there are matching issues both on the query and main sheet, to paste those rows to the main sheet, without putting them in the last row, rather taking their place(for ex. : If issue 4405 is both on Datatbl(query sheet) and Maintbl(main sheet). It is located in row 22(last row) on the query but in row 20 on main. Some of the data in columns of row 22 are updated in the query and need to take place on main in the row that was in (20) ). I hope I presented my problem as it is, and will give the code below.
Option Explicit
Public DataTbl, MainTbl, CompletedTbl As ListObject
Public MainLastRow As Long
Sub RefreshAndSearch(control As IRibbonControl)
'Search Data And Import Originals In Main / Variables
Set DataTbl = ThisWorkbook.Worksheets("PENDING Inbounds (RMA)").ListObjects("tbl_data")
Set MainTbl = ThisWorkbook.Worksheets("Main Tab").ListObjects("tbl_main")
Dim DataIssue, MainIssue As Range
Set DataIssue = DataTbl.ListColumns(1).DataBodyRange
Set MainIssue = MainTbl.ListColumns(1).DataBodyRange
Dim Issue As Range
Dim MainFound As Range
ThisWorkbook.Connections("Query - PENDING Inbounds (RMA)").Refresh
Application.CalculateUntilAsyncQueriesDone
'Loop For Searching Issues From Data In Main
For Each Issue In DataIssue
Set MainFound = MainIssue.Find(Issue)
If MainFound Is Nothing Then
If ThisWorkbook.Worksheets("Main Tab").Range("A2") = "" Then
DataTbl.ListRows(Issue.Row - 1).Range.Copy
MainLastRow = MainTbl.Range.Rows.Count
ThisWorkbook.Worksheets("Main Tab").Range("A" & MainLastRow).PasteSpecial Paste:=xlPasteValues
MainTbl.Range(MainLastRow, "AB") = Date
Else
DataTbl.ListRows(Issue.Row - 1).Range.Copy
MainLastRow = MainTbl.Range.Rows.Count
ThisWorkbook.Worksheets("Main Tab").Range("A" & MainLastRow + 1).PasteSpecial Paste:=xlPasteValues
MainTbl.Range(MainLastRow, "AB") = Date
End If
End If
Next Issue
ThisWorkbook.Worksheets("Main Tab").Range("A2").Select
'No New Issues Message
If Not MainFound Is Nothing Then
MsgBox "No new issues found.", , "Attention"
End If
End Sub