VBA to paste a cell value in multiple sheets

[Mr.Karr]

Hi

Can anyone please provide a code snippet to paste a cell value in multiple sheets (but same cell with all sheets). Is this possible with array?

Worksheets("Home").Range("A1").Copy _
    Destination:=Worksheets(Array("Sheet1", "Sheet2")).Range("H16")

Copy a value from home tab cell A1 & paste that into sheet1, sheet2 (cell H16)

Thanks

 

[Bhawani]

Please try below code

Sub TEST()
Dim sht As Worksheet
Sheets("Home").Range("A1").Copy
For Each sht In Worksheets
sht.Range("H16").PasteSpecial xlPasteValues
Next
Application.CutCopyMode = False
End Sub

 

[Mr.Karr]

@Bhawani : there is a catch here. Not into all sheets but selected ones.

 

[Bhawani]

Try below code
'Sheets selection should be done before running macro

Sub TEST()
Dim sht As Worksheet
Sheets("sheet1").Range("a1").Copy
'Sheets selection should be done before running macro
Selection.Range("H16").PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub