Search for cell equal to cell in another worksheet

[hnowack]

Hi all,

I'm trying to search for a cell in the Cardio Database worksheet that is equal to a specific cell in the Design Cardio worksheet (B3). Once it finds a match I want it to copy the range of cells below that "found" cell in the Cardio Database, and paste it in the Print 6Day worksheet (A4:I19). The search needs to look across row 1, then jump down to row 21, 41, 61, etc. if it does not find a matching cell.

This is what I have so far:

Sub SearchCardio()

Application.ScreenUpdating = False
Sheets("Cardio Database").Select
Range("A1").Select

Do Until ActiveCell.Value = ""
If ActiveCell.Value = Sheets("Design Cardio").B3 Then
    ActiveCell.Select
    Exit Do
ElseIf ActiveCell.Value <> Sheets("Design Cardio").B3 Then
    ActiveCell.Offset(0, 1).Select
End If
Loop

End Sub

Thanks for any help! I've attached a short version of the workbook to aid anyone that needs further explanation.

[rylo]

Hi

How about

Sub aaa()
  Set findit = Sheets("Cardio Database").Cells.Find(what:=Range("B3").Value)
  findit.Offset(1, 0).Resize(18, 8).Copy
  With Sheets("Print 6Day")
    .Range("A4").PasteSpecial (xlPasteValues)
    .Range("A4").PasteSpecial (xlPasteFormats)
  End With
  
End Sub

It does assume that the match will always be found.

rylo

[hnowack]

Thanks Rylo. I think I can make that work. My challenge for this VBA noobie is to reformat/clear out the 'Print 6Day' cells in case I run the macro multiple times. I get a runtime error every time there's already a cardio workout copied into Print 6Day. A copy and paste of blank cells isn't working.

Any suggestions?

[rylo]

Hi

as you are taking your formats from the copied data, try

Sub aaa()
  Sheets("Print 6Day").Range("A4:H21").Clear
  Set findit = Sheets("Cardio Database").Cells.Find(what:=Range("B3").Value)
  findit.Offset(1, 0).Resize(18, 8).Copy
  With Sheets("Print 6Day")
    .Range("A4").PasteSpecial (xlPasteValues)
    .Range("A4").PasteSpecial (xlPasteFormats)
  End With
  
End Sub

rylo

[hnowack]

works great. this small macro is gonna help me out tremendously. A few more additions, and it'll be all set up.