Repeat value in the same cell on each sheet
 

I'm creating a workbook that records staff sick days and am trying to find an
easy way for the secretary to add and delete staff members as they join and
leave the company. The workbook has a Summary sheet and then a sheet for each
month of the year.

The code I've done below puts the name (keyed into a tiny userform by the
secretary) into the summary sheet, but i need that name to be placed into the
same cell on every sheet in the workbook. I suspect this is fiendishly simple
but i'm drawing a blank. Any suggestions please?



Sub cmdAddStaffMember_click()
FindFirstEmptyRow
'set the value of the first blank cell in column A to the name in the textbox
Cells(intBlankRow, 1).Value = txtAddStaffName.Value
'reset whats in the textbox
txtAddStaffName.Value = vbNullString
frmAdd.Hide
End Sub

Function FindFirstEmptyRow()
'specify row numbers to be searched
For intRow = 1 To 100
'check if the cell in column A is empty
If IsEmpty(Cells(intRow, 1)) Then
'if so, set that variable to the row number and quit
intBlankRow = intRow
Exit Function
End If
'if not, carry on
Next intRow
End Function

Repeat value in the same cell on each sheet
 

Sub cmdAddStaffMember_click()
FindFirstEmptyRow
'set the value of the first blank cell in column A to the name in the
textbox
for each sh in thisworkbook.worksheets
sh.Cells(intBlankRow, 1).Value = txtAddStaffName.Value
Next sh
'reset whats in the textbox
txtAddStaffName.Value = vbNullString
frmAdd.Hide
End Sub

--
Regards,
Tom Ogilvy


"Katherine" wrote in message
...
I'm creating a workbook that records staff sick days and am trying to find
an
easy way for the secretary to add and delete staff members as they join
and
leave the company. The workbook has a Summary sheet and then a sheet for
each
month of the year.

The code I've done below puts the name (keyed into a tiny userform by the
secretary) into the summary sheet, but i need that name to be placed into
the
same cell on every sheet in the workbook. I suspect this is fiendishly
simple
but i'm drawing a blank. Any suggestions please?



Sub cmdAddStaffMember_click()
FindFirstEmptyRow
'set the value of the first blank cell in column A to the name in the
textbox
Cells(intBlankRow, 1).Value = txtAddStaffName.Value
'reset whats in the textbox
txtAddStaffName.Value = vbNullString
frmAdd.Hide
End Sub

Function FindFirstEmptyRow()
'specify row numbers to be searched
For intRow = 1 To 100
'check if the cell in column A is empty
If IsEmpty(Cells(intRow, 1)) Then
'if so, set that variable to the row number and quit
intBlankRow = intRow
Exit Function
End If
'if not, carry on
Next intRow
End Function

Repeat value in the same cell on each sheet
 

or with the added variable declared in case you use option explicit

Sub cmdAddStaffMember_click()
Dim Sh as Worksheet
FindFirstEmptyRow
'set the value of the first blank cell in column A to the name in the
textbox
for each sh in thisworkbook.worksheets
sh.Cells(intBlankRow, 1).Value = txtAddStaffName.Value
Next sh
'reset whats in the textbox
txtAddStaffName.Value = vbNullString
frmAdd.Hide
End Sub

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
Sub cmdAddStaffMember_click()
FindFirstEmptyRow
'set the value of the first blank cell in column A to the name in the
textbox
for each sh in thisworkbook.worksheets
sh.Cells(intBlankRow, 1).Value = txtAddStaffName.Value
Next sh
'reset whats in the textbox
txtAddStaffName.Value = vbNullString
frmAdd.Hide
End Sub

--
Regards,
Tom Ogilvy


"Katherine" wrote in message
...
I'm creating a workbook that records staff sick days and am trying to
find
an
easy way for the secretary to add and delete staff members as they join
and
leave the company. The workbook has a Summary sheet and then a sheet for
each
month of the year.

The code I've done below puts the name (keyed into a tiny userform by
the
secretary) into the summary sheet, but i need that name to be placed
into
the
same cell on every sheet in the workbook. I suspect this is fiendishly
simple
but i'm drawing a blank. Any suggestions please?



Sub cmdAddStaffMember_click()
FindFirstEmptyRow
'set the value of the first blank cell in column A to the name in the
textbox
Cells(intBlankRow, 1).Value = txtAddStaffName.Value
'reset whats in the textbox
txtAddStaffName.Value = vbNullString
frmAdd.Hide
End Sub

Function FindFirstEmptyRow()
'specify row numbers to be searched
For intRow = 1 To 100
'check if the cell in column A is empty
If IsEmpty(Cells(intRow, 1)) Then
'if so, set that variable to the row number and quit
intBlankRow = intRow
Exit Function
End If
'if not, carry on
Next intRow
End Function

Repeat value in the same cell on each sheet
 

Thanks Tom, but what do I need to define the variable sh as?

"Tom Ogilvy" wrote:

Sub cmdAddStaffMember_click()
FindFirstEmptyRow
'set the value of the first blank cell in column A to the name in the
textbox
for each sh in thisworkbook.worksheets
sh.Cells(intBlankRow, 1).Value = txtAddStaffName.Value
Next sh
'reset whats in the textbox
txtAddStaffName.Value = vbNullString
frmAdd.Hide
End Sub

--
Regards,
Tom Ogilvy


"Katherine" wrote in message
...
I'm creating a workbook that records staff sick days and am trying to find
an
easy way for the secretary to add and delete staff members as they join
and
leave the company. The workbook has a Summary sheet and then a sheet for
each
month of the year.

The code I've done below puts the name (keyed into a tiny userform by the
secretary) into the summary sheet, but i need that name to be placed into
the
same cell on every sheet in the workbook. I suspect this is fiendishly
simple
but i'm drawing a blank. Any suggestions please?



Sub cmdAddStaffMember_click()
FindFirstEmptyRow
'set the value of the first blank cell in column A to the name in the
textbox
Cells(intBlankRow, 1).Value = txtAddStaffName.Value
'reset whats in the textbox
txtAddStaffName.Value = vbNullString
frmAdd.Hide
End Sub

Function FindFirstEmptyRow()
'specify row numbers to be searched
For intRow = 1 To 100
'check if the cell in column A is empty
If IsEmpty(Cells(intRow, 1)) Then
'if so, set that variable to the row number and quit
intBlankRow = intRow
Exit Function
End If
'if not, carry on
Next intRow
End Function

Repeat value in the same cell on each sheet
 

Excellent, thanks :)

I was doing a 'Dim sh as sheet' for some reason - D'oh! No wonder it wasn't
working.

"Tom Ogilvy" wrote:

or with the added variable declared in case you use option explicit

Sub cmdAddStaffMember_click()
Dim Sh as Worksheet
FindFirstEmptyRow
'set the value of the first blank cell in column A to the name in the
textbox
for each sh in thisworkbook.worksheets
sh.Cells(intBlankRow, 1).Value = txtAddStaffName.Value
Next sh
'reset whats in the textbox
txtAddStaffName.Value = vbNullString
frmAdd.Hide
End Sub

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
Sub cmdAddStaffMember_click()
FindFirstEmptyRow
'set the value of the first blank cell in column A to the name in the
textbox
for each sh in thisworkbook.worksheets
sh.Cells(intBlankRow, 1).Value = txtAddStaffName.Value
Next sh
'reset whats in the textbox
txtAddStaffName.Value = vbNullString
frmAdd.Hide
End Sub

--
Regards,
Tom Ogilvy


"Katherine" wrote in message
...
I'm creating a workbook that records staff sick days and am trying to
find
an
easy way for the secretary to add and delete staff members as they join
and
leave the company. The workbook has a Summary sheet and then a sheet for
each
month of the year.

The code I've done below puts the name (keyed into a tiny userform by
the
secretary) into the summary sheet, but i need that name to be placed
into
the
same cell on every sheet in the workbook. I suspect this is fiendishly
simple
but i'm drawing a blank. Any suggestions please?



Sub cmdAddStaffMember_click()
FindFirstEmptyRow
'set the value of the first blank cell in column A to the name in the
textbox
Cells(intBlankRow, 1).Value = txtAddStaffName.Value
'reset whats in the textbox
txtAddStaffName.Value = vbNullString
frmAdd.Hide
End Sub

Function FindFirstEmptyRow()
'specify row numbers to be searched
For intRow = 1 To 100
'check if the cell in column A is empty
If IsEmpty(Cells(intRow, 1)) Then
'if so, set that variable to the row number and quit
intBlankRow = intRow
Exit Function
End If
'if not, carry on
Next intRow
End Function