Refresh Cell macro

[cadamhill]

I have a cell A1 in my estimate sheet that does a concatenate of values entered in the estimate for and places these values in the header of several different sheet (proposal, ....yada yada)..Every time I enter the job number in the cell in the estimate form, it populates to that value in to the concatenate of cell A1. The only problem is that I have to physically select cell A1 in the estimate and press enter on my keyboard to get the action to submit to each page in the header. I want to have a button where I can push that will refesh, so to speak, distributing this formula across my pages. Here is what I currently have. (someone here helped me with this 3 years ago. )


Sub HeaderRefresh()

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet
Dim strHeader As String

If Target.Cells.Count > 1 Then Exit Sub

If Target.Address = "$A$1" Then
If Target <> vbNullString Then
strHeader = Range("A1").Value
For Each ws In ThisWorkbook.Worksheets
With ws.PageSetup
.CenterHeader = strHeader
End With
Next ws
End If
End If
End Sub
End Sub

(Sorry, I don't know how to attach code in VB form.) I want to use this code with the appropriate modification to allow for a push of a button to execute cell A1. Thanks for any help.

[walruseggman]

Edit: Oops, I didn't even notice your code wasn't in tags. I shouldn't have responded until it is corrected. Edit your post, highlight the code, then press the # button in the text editor.

Insert an ActiveX button (Developer Tab > Controls > Insert) then put your code inside the _Click sub for that button, like below. Make sure the sub is named to match your button's name.

btw, i'm not sure why your code shows a sub in a sub, but that's not allowed.

Please Login or Register  to view this content.