VBA IndexMatch then Paste Values

[phillip87]

The below correctly checks the date and pastes in the values into the correct sheet/column.

However.......

1. I am needing this to paste under the date not over the top of it
2. I need this to only copy the values where both the date and category match

Sheet 2 is a dynamic table so the categories may or may not be listed depending on if they have a value which is why I need to INDEXMATCH rather than just copy a range.

Sub findAndCopy()
  Dim foundCell As Range, sh2, sh As Worksheet
 
  'Set sheets
  Set sh2 = Sheets("Refresh Data Report")
  Set sh3 = Sheets("Ongoing Record")
 
  'Find string in column C of Sheet2
  Set foundCell = sh3.Range("B:H").Find(sh2.Range("AP1").Value, , xlValues, xlWhole)
  If Not foundCell Is Nothing Then 'If match cell is found
    sh2.Range("AI3:AI30").Copy
    foundCell.PasteSpecial xlPasteValues
    foundCell.PasteSpecial xlPasteFormats
    Application.CutCopyMode = False
  Else
    Call MsgBox("Not found the match cell!", vbExclamation, "Finding String")
  End If
End Sub

 

[Candyman8019]

Try:

foundCell.offset(1,0) PasteSpecial xlPasteValues
foundCell.offset(1,0) PasteSpecial xlPasteFormats

 

[phillip87]

Try:

foundCell.offset(1,0) PasteSpecial xlPasteValues
foundCell.offset(1,0) PasteSpecial xlPasteFormats

This is now giving me a 'syntax' error if I swop my 2 line of code for this

 

[Candyman8019]

Sorry. I missed a period.

Try

foundCell.offset(1,0).PasteSpecial xlPasteValues
foundCell.offset(1,0).PasteSpecial xlPasteFormats

 

[phillip87]

Sorry. I missed a period.

Try

foundCell.offset(1,0).PasteSpecial xlPasteValues
foundCell.offset(1,0).PasteSpecial xlPasteFormats

OMG haha sorry I thought I had tried that when troubleshooting but obviously not.

That defiantly fixes the positioning of the paste functionality against the date however I am still needing to specify which values to copy based on the categories shown.

The main table changes with what categories and values we are dealing with on a daily basis, however these categories shown are the main ones we want to continue to track so if they are listed we want to save their values.

I have attached a screenshot of the main table, we use the table to manage the daily workload however we only want to track 6 or 7 of these via the copy/paste to the other sheet as per the code. The reason I need an indexmatch is because their position in the table may change or they may not be listed at all on a given day.

 

[Candyman8019]

What version of Excel are you using?

 

[Candyman8019]

Also, without seeing your columns in the screenshots, it's difficult to picture what you're searching for and what you're copying when you find it. Are you able to use xl2bb add in or share a copy of the workbook?

 

[phillip87]

What version of Excel are you using?

I am pretty sure it is V16

 

[phillip87]

Also, without seeing your columns in the screenshots, it's difficult to picture what you're searching for and what you're copying when you find it. Are you able to use xl2bb add in or share a copy of the workbook?

"Capture 2"is the main table that updates based on the data at the time we are looking at it so we can assign work. The categories and values change based on what is coming in.

"Capture" is the ongoing report where we are wanting to save the values against the listed categories for ongoing review to ensure numbers are decreasing over time. we do not need every category ever listed just the ones we have show as these are the most important.

Everything in "Capture 2" is working perfectly, what I am trying to get working is an index/match copy and paste from the table to the ongoing record based on the category but also based on the date. The copy/paste via date in the VBA is working but we also need to only pully the values for the listed caregories.

 

[Candyman8019]

I still don't see what value is in AP1.