Copy a column from one sheet to multiple sheets based on sheetname

0
votes

I am trying to copy column A from one sheet "OPT1" to the same column in multiple sheets - "OPT1_1", OPT1_2" etc

but it doesn't seem to like the range?

Sub Copy_MN()

Dim ws As Worksheet

  For Each ws In Worksheets
  Sheets("OPT1").Select
  Range("A:A").Copy
    If ws.Name Like "OPT1_*" Then
    'ActiveSheet.Select
        ws.Range("A:A").Select
        ActiveSheet.Paste
    End If
    Next ws

End Sub

1
excelvba

1 Answers

1
votes

Here's my proposition:

Sub Copy_MN()
Dim ws As Worksheet

  For Each ws In Worksheets
    If ws.name Like "OPT1_*" Then
        ws.Range("A:A").Value = Sheets("OPT1").Range("A:A").Value
    End If
    Next ws
End Sub