VBA - Copy / paste range based on matching a cell value to sheet name..

[BenGee]

Hi

I'm struggling with the copy / paste part of my code. An example of what I'm trying to do is, if cell B1 in workheets("Rota") matches the name of a worksheet, then copy range B3:B33 and paste this into the matching worksheet in the range V3:V33. I want to do this for each match cell in the range B1:Z1 in worksheets("Rota") and the range to copy is always the adjacent cells in rows 3 to 33.

My Code so far is;

Dim ws As Worksheet
Dim Target As Range
Dim CopyData As Range
Dim PasteTarget As Range

Set Target = Worksheets("Rota").Range("B1:Z1")
Set CopyData = ActiveCell.Offset(2, 0).Resize(31, 0)
Set PasteTarget = Range("V3").Resize(31, 0)

For Each ws In Sheets
For Each Cell In Target
If Cell.Value = ws.Name Then
CopyData.Copy Destination:=PasteTarget
End If
Next Cel
Next ws

 

[Fluff]

How about

Sub CopyToShts()
   Dim Cl As Range
   
   For Each Cl In Sheets("Rota").Range("B1:Z1")
      If Evaluate("isref('" & Cl.Value & "'!A1)") Then
         Cl.Offset(, 2).Resize(31).copy Sheets(Cl.Value).Range("V3")
      End If
   Next Cl
End Sub