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
|