+ Reply to Thread
Results 1 to 5 of 5

Repeat value in the same cell on each sheet

  1. #1
    Katherine
    Guest

    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

  2. #2
    Tom Ogilvy
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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




  3. #3
    Tom Ogilvy
    Guest

    Re: 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" <[email protected]> wrote in message
    news:%[email protected]...
    > 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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

    >
    >




  4. #4
    Katherine
    Guest

    Re: 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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

    >
    >
    >


  5. #5
    Katherine
    Guest

    Re: 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" <[email protected]> wrote in message
    > news:%[email protected]...
    > > 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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

    > >
    > >

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1