One userform, to fill several workbooks

[JasonEnsor]

I have a masterfile that has on it a userform, when the user fills in this form a copy of the data is filtered on to the masterfile, however certain columns of data need to also copy to a second file called EquipmentCenter and then depending on a users choice data is also copied to a 3rd workbook.

i can get all these to work as seperate sheets on one workbook, however i need the seperate workbooks due to the information that is stored in the main file has confidential data that the other workbooks do not need.

Any ideas?

 

[fumei]

Use Worksheets objects. Once you declare and set them as separate objects you can do whatever you want with them.


unknown

 

[JasonEnsor]

I have used the following code, however i keep getting an error saying subscript out of range....any ideas?

Dim EquipmentC As Worksheet ' Set for Equipment Center
Dim ws As Worksheet 'Set it for MasterFile

EquipmentC = Worksheets("EquipmentCenter.xls").Sheets("Summary")
ws = Worksheets("Support Plans.xls").Sheets(SchoolSelect.Value)

 

[fumei]

If you declare an object, you must SET the object.

Set EquipmentC = Worksheets("EquipmentCenter.xls").Sheets("Summary")


unknown

 

[JasonEnsor]

I think i must be missing something because it is still saying the same thing.

It shouldnt be this hard to copy data to 3 separate workbooks should it? I bet it is something stupid that i am missing.

 

[xlbo]

I would've thought you would need to open the workbook before you can reference a sheet in it...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Dim EquipmentC As Worksheet ' Set for Equipment Center
Dim ws As Worksheet 'Set it for MasterFile

[b]Set [/b]EquipmentC = Work[b]book[/b]s("EquipmentCenter.xls").Sheets("Summary")
[b]Set [/b]ws = Worksheets("Support Plans.xls").Sheets(SchoolSelect.Value)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JasonEnsor]

Hey,

Thank you all for your answers, i am however still at a loss with this one. i have pasted the code that goes in to my UserForm, maybe that will help someone spot where i am going wrong

Dim EquipmentC As Worksheet ' Set for Equipment Center
Dim ws As Worksheet 'Set it for MasterFile
Dim Requirement As Worksheet ' Set for Master Support Plan school
Dim iRow As Long

Sub SaveButton_Click()

Set EquipmentC = Workbooks("EquipmentCenter.xls").Sheets("Summary")
Set ws = Worksheets("Support Plans.xls").Sheets(SchoolSelect.Value)

With EquipmentC
    iRow = EquipmentC.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
    EquipmentC.Cells(iRow, 1).Value = IDNumber.Value
End With


With ws

    iRow = ws.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
    
    .Cells(iRow, 1).Value = IDNumber.Value
    .Cells(iRow, 2).Value = LastName
    .Cells(iRow, 3).Value = FirstName.Value
    .Cells(iRow, 4).Value = ProgrammeCode.Value
    .Cells(iRow, 5).Value = Status.Value
    .Cells(iRow, 6).Value = ProgrammeStart.Value
    .Cells(iRow, 7).Value = Module1.Value
    .Cells(iRow, 8).Value = Module2.Value
    .Cells(iRow, 9).Value = Module3.Value
    .Cells(iRow, 10).Value = Module4.Value
    .Cells(iRow, 11).Value = Module5.Value
    .Cells(iRow, 12).Value = Module6.Value
    .Cells(iRow, 13).Value = Module7.Value
    .Cells(iRow, 14).Value = Module8.Value
    .Cells(iRow, 15).Value = Module9.Value
    .Cells(iRow, 16).Value = Module10.Value
    .Cells(iRow, 17).Value = ExamRequirements.Value
    .Cells(iRow, 18).Value = ReasonForPlan.Value

End With

With ExamRequirements.Value

If ExamRequirements.Value = "Extra Time" Then

    Set Requirement = Application.Workbooks("ExtraTime.xls").Sheets(SchoolSelect.Value)

ElseIf ExamRequirements.Value = "Additional Support" Then
        
    Set Requirement = Application.Workbooks("AdditionalSupport.xls").Sheets(SchoolSelect.Value)

End If

End With

With Requirement

    .Cells(iRow, 1).Value = IDNumber.Value
    .Cells(iRow, 2).Value = LastName
    .Cells(iRow, 3).Value = FirstName.Value

End With

End Sub

Private Sub SchoolSelect_Change()

If SchoolSelect.Value = "Art & Design" Then
    NewSupportPlan.ProgrammeCode.Clear
    
    With NewSupportPlan
        .ProgrammeCode.RowSource = "ArtDes"
    End With
    
ElseIf SchoolSelect.Value = "Technology" Then
       
    NewSupportPlan.ProgrammeCode.Clear
        
    With NewSupportPlan
        .ProgrammeCode.RowSource = "Tech"
        
    End With
        
ElseIf SchoolSelect.Value = "Humanities" Then

    NewSupportPlan.ProgrammeCode.Clear
        
    With NewSupportPlan
        .ProgrammeCode.RowSource = "Hum"
    End With
    
ElseIf SchoolSelect.Value = "" Then

    NewSupportPlan.ProgrammeCode.Clear
    SchoolSelect.SetFocus
    MsgBox "Please Select a School"
        
End If

End Sub

Public Sub CloseBtn_Click()
    
    Unload Me
    
End Sub

I just can't seem to get this to work, it keeps saying that subscript is out of range. all the files are stored in the same directory.

Jason

 

[xlbo]

Dim EquipC_WB as workbook

Set EquipC_WB = workbooks.open(thisworkbook.path & "\" & EquipmentCenter.xls)
Set EquipmentC = EquipC_WB.Sheets("Summary")
Set ws = Thisworkbook.Sheets(SchoolSelect.Value)


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question