Copying Data to Worksheet from UserForm

[Dooha]

Dear All,

I've got a spreadsheet that contains a userform. My userform works and does everything I want it to do. I copy the data entered from the user into the sheet I want. I'd like to duplicate that data into a different workbook too.
So, containing the userform in only 1 file, I'd like to copy the data in 2 different files.

I've got this bit of code:

If CGHI Then
Cells(NextRow, 7) = "a"

Set ws2 = Workbooks("HI.xls").Sheets("Airframe")
'Determine next empty row
iRow = _
Application.WorksheetFunction.CountA(Range("A:A")) + 1
'Transfer Data
ws2.Cells(iRow, 1) = PubliType.Value
ws2.Cells(iRow, 2) = PubliNum.Text
ws2.Cells(iRow, 3) = RevNum.Text
ws2.Cells(iRow, 4) = PubliTitle.Text
ws2.Cells(iRow, 5) = ComplianceType.Value
ws2.Cells(iRow, 6) = DateBox.Text

End If
I have defined ws2 as: Dim ws2 As Worksheet

Can anyone help me???

Thanks!!!!

 

[Randall00]

Hmm, so is ws2 the one that works correctly? If so, did you try defining the other worksheet as "ws3" and applying the same code?

 

[Dooha]

ws2 is the one that does not work...
I get the following error message: run time error '9'
susbcript out of range

 

[Randall00]

Hmm, well I think you probably at least need .Value on the end of the 'Cells' statement:

ws2.Cells(iRow, 1).Value = PubliType.Value

Other than that though, if you have code that works on one sheet, it should also work for the other. What does the code that works look like? And how have you defined the worksheet in that code?

 

[Dooha]

This is the bit that works:

Private Sub OKButton_Click()
Dim NextRow As Long

'Determine which sheet
If OptionAF Then
'Make sure Sheet is active
Sheets("Airframe").Activate

'Determine next empty row
NextRow = _
Application.WorksheetFunction.CountA(Range("A:A")) + 1

'Transfer Data
Cells(NextRow, 1) = PubliType.Value
Cells(NextRow, 2) = PubliNum.Text
Cells(NextRow, 3) = RevNum.Text
Cells(NextRow, 4) = PubliTitle.Text
Cells(NextRow, 5) = ComplianceType.Value
Cells(NextRow, 6) = DateBox.Text
End If

I'd like to transfer the same data to a different worksheet

 

[Randall00]

Okay, so even though the worksheets have the same name, I'm going to assume that the workbooks have two different names and are both open when this code is being run.

Give this a shot anyway--I can't say I've tested it, but it cleans up a few of the issues (I hope).

Dim ws1 as Worksheet
Set ws1 = ThisWorkbook.Sheets("Airframe")
Dim ws2 as Worksheet
Set ws2 = Workbooks("HI.xls").Sheets("Airframe")

Dim NextRow as Long

If OptionAF Then
[INDENT]'Make sure Sheet is active
ws1.Activate

NextRow = Application.WorksheetFunction.CountA(ws1.Range("A:A")) + 1

With ws1
[INDENT].Cells(NextRow, 1) = PubliType.Value
.Cells(NextRow, 2) = PubliNum.Text
.Cells(NextRow, 3) = RevNum.Text
.Cells(NextRow, 4) = PubliTitle.Text
.Cells(NextRow, 5) = ComplianceType.Value
.Cells(NextRow, 6) = DateBox.Text[/INDENT]
End With

NextRow = Application.WorksheetFunction.CountA(ws2.Range("A:A")) + 1

With ws2
[INDENT].Cells(NextRow, 1) = PubliType.Value
.Cells(NextRow, 2) = PubliNum.Text
.Cells(NextRow, 3) = RevNum.Text
.Cells(NextRow, 4) = PubliTitle.Text
.Cells(NextRow, 5) = ComplianceType.Value
.Cells(NextRow, 6) = DateBox.Text[/INDENT]
End With[/INDENT]

Else
End If