Like to put it in VBA please

[Steved]

Hello from Steved

How would I go about putting the below into VBA please.

=IF(ISNUMBER(SMALL(IF($S$6:$S$106="","",ROW($S$6:$S$106)),ROW(1:1))),INDIRECT("Summary!"&CHAR(COLUMN()+64)&SMALL(IF($S$6:$S$106="","",ROW($S$6:$S$106)),ROW(1:1))),"")

Thankyou.

 

[Gary Keramidas]

will this work for you?

Sub test()
Range("A1").Formula = "=IF(ISNUMBER(SMALL(IF($S$6:$S$106="""",""""," & _
"ROW($S$6:$S$106)),ROW(1:1))),INDIRECT(""Summary!""&CHAR(COLUMN()+64)&SMALL(IF($S$6:$S$106="""","""","
& _
"ROW($S$6:$S$106)),ROW(1:1))),"""")"

End Sub

 

[Steved]

Hello Gary from Steved

Gary The below is run from the sheet I have named summary

I have ten sheets hence I need a event procedure, so if
somebody types in summary sheet it is then updated in another sheet

Each sheet is represented by a City name and have a formula in each off the
sheets
to put the data only associated with the city. ie if somebody types in the
Summary sheet something for say a sheet i've called Washington the the data
will go into that sheet.

Hopefully you have alook at this further.

Thankyou.