VBA Help - Index match, then copy/paste

[Nordicrx8]

Hey all!

I'm working on a new project, and I'd like to incorporate some VBA to make life easier for the end users. I'm still very new at VBA, and I'm having a hard time wrapping my head around the code I found online. (A lot of times I can deconstruct it and make sense out of it)

What I'm trying to do is this. I have two tabs in a workbook. The data I want to copy/paste is located in C22:AB22. (On the "Conversion" Tab) The DATE for this data is located in cell E29 on the same tab.

By clicking a button, I want the code to copy range C22:AB22 from "Conversion", search for the matching date in the other tab (Named "MTD") Cell range: A2:A32, and paste the data in the corresponding row. I'd also like it to paste as values AND paste formatting. I don't want to carry over the formulas. Basically an index/match, copy/paste with a click of a button.

I hope this makes sense - Please let me know if you need additional information from me on this.

Thanks so much in advance for your help!

 

[Nordicrx8]

Bump to the top - any help would be appreciated! Happy Friday all!

 

[Nordicrx8]

Never mind - I think I got it! Thanks anyway all - have a great weekend!


Option Explicit
Sub findAndCopy()


Dim foundCell As Range
Dim strFind As String
Dim fRow, fCol As Integer
Dim sh1, sh2 As Worksheet

'Set sheets
Set sh1 = Sheets("Conv")
Set sh2 = Sheets("MTD.Data")

'Get find string
strFind = sh1.Range("E29").Value

'Find string in column C of Sheet2
Set foundCell = sh2.Range("A:AF").Find(strFind, LookIn:=xlValues)

'If match cell is found
If Not foundCell Is Nothing Then

'Get the row and column
fRow = foundCell.Row
fCol = foundCell.Column

'paste in range R3 of Sheet1
sh1.Range("B22:ab22").copy

sh2.Range(Cells(fRow + 0, fCol).Address & ":" & Cells(fRow + 0, fCol).Address).PasteSpecial xlPasteValues

'Clear cache
Application.CutCopyMode = False

'If not found, show message.
Else

Call MsgBox("Not found the match cell!", vbExclamation, "Finding String")

End If

End Sub

 

[DanteAmor]

Here a simplified way of the macro for you to consider.

Sub findAndCopy()
  Dim foundCell As Range, sh1, sh2 As Worksheet
  
  'Set sheets
  Set sh1 = Sheets("Conv")
  Set sh2 = Sheets("MTD.Data")
  
  'Find string in column C of Sheet2
  Set foundCell = sh2.Range("A:AF").Find(sh1.Range("E29").Value, , xlValues, xlWhole)
  If Not foundCell Is Nothing Then 'If match cell is found
    sh1.Range("B22:ab22").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

 

[Nordicrx8]

Here a simplified way of the macro for you to consider.

Sub findAndCopy()
  Dim foundCell As Range, sh1, sh2 As Worksheet
 
  'Set sheets
  Set sh1 = Sheets("Conv")
  Set sh2 = Sheets("MTD.Data")
 
  'Find string in column C of Sheet2
  Set foundCell = sh2.Range("A:AF").Find(sh1.Range("E29").Value, , xlValues, xlWhole)
  If Not foundCell Is Nothing Then 'If match cell is found
    sh1.Range("B22:ab22").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

That worked perfectly - thank you!!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.