entering data into multiple sheets

R

Ranae

Good morning,
I am entering data into a worksheet with a userform. I need to enter the
exact same information into another worksheet (2010). Is there a way to do
this without having to write the same code for the second worksheet? Thank
you in advance.
Ranae


Dim Wbk As Workbook
Set Wbk = Workbooks("Reworklog")
Sheets("2006-2010").Select

Dim LastRow As Object
Set LastRow = Range("a65536").End(xlUp)
LastRow.Offset(1, 0) = Me.TBRecDate
LastRow.Offset(1, 1) = Me.TbDate
LastRow.Offset(1, 2) = Me.TbBoardType
LastRow.Offset(1, 3) = Me.cboboard
LastRow.Offset(1, 5) = Me.TbBatch
LastRow.Offset(1, 6) = Me.TbSN
LastRow.Offset(1, 7) = Me.cboDefectCode
LastRow.Offset(1, 8) = Me.cboProdSpec
LastRow.Offset(1, 9) = Me.cboCM
LastRow.Offset(1, 10) = Me.cboTech
LastRow.Offset(1, 11) = Me.TBreject
LastRow.Offset(1, 12) = Me.TBfinding
LastRow.Offset(1, 13) = Me.TBAction
LastRow.Offset(1, 14) = Me.TBLocation
LastRow.Offset(1, 15) = Me.cboResults
LastRow.Offset(1, 16) = Me.cboDisposition
LastRow.Offset(1, 21) = Me.cboPN1
LastRow.Offset(1, 22) = Me.cboPN2
LastRow.Offset(1, 23) = Me.cboPN3
LastRow.Offset(1, 24) = Me.cboPN4
LastRow.Offset(1, 20) = Me.TbSum

If Me.OptDbug1.Value Then
LastRow.Offset(1, 25) = Me.OptDbug1.Caption
ElseIf Me.OptDBug2.Value Then
LastRow.Offset(1, 25) = Me.OptDbug1.Caption
Else
If Me.OptDbug3.Value Then
LastRow.Offset(1, 25) = Me.OptDbug1.Caption
End If
End If
'Clear fields for next entry
Me.TbSN = ClearContents
Me.cboDefectCode = ClearContents
Me.TBreject = ClearContents
Me.TBfinding = ClearContents
Me.TBAction = ClearContents
Me.TBLocation = ClearContents
Me.OptDbug1 = ClearContents
Me.OptDBug2 = ClearContents
Me.OptDbug3 = ClearContents
Me.cboResults = ClearContents
Me.cboDisposition = ClearContents
Me.cboPN1 = ClearContents
Me.cboPN2 = ClearContents
Me.cboPN3 = ClearContents
Me.cboPN4 = ClearContents
Me.Tbcost1 = ClearContents
Me.Tbcost2 = ClearContents
Me.tbCost3 = ClearContents
Me.TbCost4 = ClearContents
Me.TbSum = ClearContents
Me.TbSN.SetFocus
End Sub
 
D

Dave Peterson

I didn't take the time to set up a userform for this...

Option Explicit
Private Sub CommandButton1_Click()
Dim Wbk As Workbook
Dim wks As Worksheet
Dim LastCell As Range 'LastRow sounds like a number to me

Set Wbk = Workbooks("Reworklog")

For Each wks In Wbk.Worksheets(Array("2006-2010", "2010"))
With wks
Set LastCell = .Cells(.Rows.Count, "A").End(xlUp)
End With

With LastCell
.Offset(1, 0).Value = Me.TBRecDate.Value
.Offset(1, 1).Value = Me.TbDate.Value
.Offset(1, 2).Value = Me.TbBoardType.Value
.Offset(1, 3).Value = Me.cboboard.Value
.Offset(1, 5).Value = Me.TbBatch.Value
.Offset(1, 6).Value = Me.TbSN.Value
.Offset(1, 7).Value = Me.cboDefectCode.Value
.Offset(1, 8).Value = Me.cboProdSpec.Value
.Offset(1, 9).Value = Me.cboCM.Value
.Offset(1, 10).Value = Me.cboTech.Value
.Offset(1, 11).Value = Me.TBreject.Value
.Offset(1, 12).Value = Me.TBfinding.Value
.Offset(1, 13).Value = Me.TBAction.Value
.Offset(1, 14).Value = Me.TBLocation.Value
.Offset(1, 15).Value = Me.cboResults.Value
.Offset(1, 16).Value = Me.cboDisposition.Value
.Offset(1, 21).Value = Me.cboPN1.Value
.Offset(1, 22).Value = Me.cboPN2.Value
.Offset(1, 23).Value = Me.cboPN3.Value
.Offset(1, 24).Value = Me.cboPN4.Value
.Offset(1, 20).Value = Me.TbSum.Value

If Me.OptDbug1.Value Then
.Offset(1, 25).Value = Me.OptDbug1.Caption
ElseIf Me.OptDBug2.Value Then
.Offset(1, 25).Value = Me.OptDbug1.Caption
ElseIf Me.OptDbug3.Value Then
.Offset(1, 25).Value = Me.OptDbug1.Caption
End If
End If
End With
Next wks

'Clear fields for next entry
Me.TbSN.Value = ""
Me.cboDefectCode.Value = ""
Me.TBreject.Value = ""
Me.TBfinding.Value = ""
Me.TBAction.Value = ""
Me.TBLocation.Value = ""
Me.OptDbug1.Value = False
Me.OptDBug2.Value = False
Me.OptDbug3.Value = False
Me.cboResults.Value = ""
Me.cboDisposition.Value = ""
Me.cboPN1.Value = ""
Me.cboPN2.Value = ""
Me.cboPN3.Value = ""
Me.cboPN4.Value = ""
Me.Tbcost1.Value = ""
Me.Tbcost2.Value = ""
Me.tbCost3.Value = ""
Me.TbCost4.Value = ""
Me.TbSum.Value = ""
Me.TbSN.SetFocus
End Sub

(Untested, uncompiled.)
 
R

Ranae

Thank you Dave! Works beautifully!


Dave Peterson said:
I didn't take the time to set up a userform for this...

Option Explicit
Private Sub CommandButton1_Click()
Dim Wbk As Workbook
Dim wks As Worksheet
Dim LastCell As Range 'LastRow sounds like a number to me

Set Wbk = Workbooks("Reworklog")

For Each wks In Wbk.Worksheets(Array("2006-2010", "2010"))
With wks
Set LastCell = .Cells(.Rows.Count, "A").End(xlUp)
End With

With LastCell
.Offset(1, 0).Value = Me.TBRecDate.Value
.Offset(1, 1).Value = Me.TbDate.Value
.Offset(1, 2).Value = Me.TbBoardType.Value
.Offset(1, 3).Value = Me.cboboard.Value
.Offset(1, 5).Value = Me.TbBatch.Value
.Offset(1, 6).Value = Me.TbSN.Value
.Offset(1, 7).Value = Me.cboDefectCode.Value
.Offset(1, 8).Value = Me.cboProdSpec.Value
.Offset(1, 9).Value = Me.cboCM.Value
.Offset(1, 10).Value = Me.cboTech.Value
.Offset(1, 11).Value = Me.TBreject.Value
.Offset(1, 12).Value = Me.TBfinding.Value
.Offset(1, 13).Value = Me.TBAction.Value
.Offset(1, 14).Value = Me.TBLocation.Value
.Offset(1, 15).Value = Me.cboResults.Value
.Offset(1, 16).Value = Me.cboDisposition.Value
.Offset(1, 21).Value = Me.cboPN1.Value
.Offset(1, 22).Value = Me.cboPN2.Value
.Offset(1, 23).Value = Me.cboPN3.Value
.Offset(1, 24).Value = Me.cboPN4.Value
.Offset(1, 20).Value = Me.TbSum.Value

If Me.OptDbug1.Value Then
.Offset(1, 25).Value = Me.OptDbug1.Caption
ElseIf Me.OptDBug2.Value Then
.Offset(1, 25).Value = Me.OptDbug1.Caption
ElseIf Me.OptDbug3.Value Then
.Offset(1, 25).Value = Me.OptDbug1.Caption
End If
End If
End With
Next wks

'Clear fields for next entry
Me.TbSN.Value = ""
Me.cboDefectCode.Value = ""
Me.TBreject.Value = ""
Me.TBfinding.Value = ""
Me.TBAction.Value = ""
Me.TBLocation.Value = ""
Me.OptDbug1.Value = False
Me.OptDBug2.Value = False
Me.OptDbug3.Value = False
Me.cboResults.Value = ""
Me.cboDisposition.Value = ""
Me.cboPN1.Value = ""
Me.cboPN2.Value = ""
Me.cboPN3.Value = ""
Me.cboPN4.Value = ""
Me.Tbcost1.Value = ""
Me.Tbcost2.Value = ""
Me.tbCost3.Value = ""
Me.TbCost4.Value = ""
Me.TbSum.Value = ""
Me.TbSN.SetFocus
End Sub

(Untested, uncompiled.)
Click to expand...
 
R

Ranae

Hi Dave,
I have one more question if you don't mind. With the code below, anywhere I
have a format statement, I get an error. Do you know why that would be?

This will give me a compile error - Wrong number of arguments or invalid
property assignment.

Private Sub Userform_Activate()
Me.TbDate = format(Now, mm / dd / yyyy)

Thanks again for your help
 
O

OssieMac

Hi Ranae,

You need to enclose the format parameter in double quotes. The empty
parenthesis with Now() are not essential but I always use them because it is
easy to see that it is a function.

Me.TbDate = Format(Now(), "mm / dd / yyyy")
 
O

OssieMac

Hi again Ranae,

I assumed that Me.TbDate is a textbox on a userform and it tests OK for me.
Is this correct? If not, then what is it?

Can I assume that you don't have any typos. When you type in Me. is TbDate
an option in the dropdown?
 
R

Ranae

Hi OssieMac,
Me.TbDate is a textbox on a userform , and it was in the list. I didn't have
any problem with the format until I changed the code from below. I'm not sure
what I did to cause the error now.
 
O

OssieMac

Hi Ranae,

It's got me. Perhaps you would like to post the code for the entire sub.
 
D

Dave Peterson

Me.TbDate.Value = format(Now, "mm/dd/yyyy")
Hi Dave,
I have one more question if you don't mind. With the code below, anywhere I
have a format statement, I get an error. Do you know why that would be?

This will give me a compile error - Wrong number of arguments or invalid
property assignment.

Private Sub Userform_Activate()
Me.TbDate = format(Now, mm / dd / yyyy)

Thanks again for your help
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

userform to update multiple sheets 1

Top