Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying data in a form into an alternative Workbook
Hello All,
I have a workbook with a form created in VB that allows data input into fields, and on command (using a Command Button) will dump the data from the form into a database on another worksheet in the same workbook, and then clears form. This all works fine. What I would like is for the data to be stored into fields on an alternative Excel Workbook as i have many users who will be entering data in the forms but I also need to consolidate the date for all users. Can i do this in the VB script inside the macro? Copy of my script for the form Below. Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Data") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row 'check for a Date If Trim(Me.txtDate.Value) = "" Then Me.txtDate.SetFocus MsgBox "You must enter a date" Exit Sub End If 'copy the data to the database ws.Cells(iRow, 1).Value = Me.txtDate.Value ws.Cells(iRow, 2).Value = Me.txtJudge.Value ws.Cells(iRow, 3).Value = Me.txtCourt.Value ws.Cells(iRow, 4).Value = Me.txtTimeStart.Value ws.Cells(iRow, 5).Value = Me.TxtTimeEnd.Value |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying data in a form into an alternative Workbook
Yes, you do it basically the same way you are now moving it to a worksheet,
but add the new workbook and range designation. example: Workbooks(2).Cells(iRow, 1).Value = Me.txtDate.Value Workbooks(2).Cells(iRow, 2).Value = Me.txtJudge.Value Workbooks(2).Cells(iRow, 3).Value = Me.txtCourt.Value Workbooks(2).Cells(iRow, 4).Value = Me.txtTimeStart.Value Workbooks(2).Cells(iRow, 5).Value = Me.TxtTimeEnd.Value Of course, you would need to define iRow for Workbooks(2), but the idea is to let VBA know that you want the value in another workbook. "dave m" wrote: Hello All, I have a workbook with a form created in VB that allows data input into fields, and on command (using a Command Button) will dump the data from the form into a database on another worksheet in the same workbook, and then clears form. This all works fine. What I would like is for the data to be stored into fields on an alternative Excel Workbook as i have many users who will be entering data in the forms but I also need to consolidate the date for all users. Can i do this in the VB script inside the macro? Copy of my script for the form Below. Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Data") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row 'check for a Date If Trim(Me.txtDate.Value) = "" Then Me.txtDate.SetFocus MsgBox "You must enter a date" Exit Sub End If 'copy the data to the database ws.Cells(iRow, 1).Value = Me.txtDate.Value ws.Cells(iRow, 2).Value = Me.txtJudge.Value ws.Cells(iRow, 3).Value = Me.txtCourt.Value ws.Cells(iRow, 4).Value = Me.txtTimeStart.Value ws.Cells(iRow, 5).Value = Me.TxtTimeEnd.Value |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Alternative to using IF function to extract data | Excel Discussion (Misc queries) | |||
UDF alternative to PULL that opens external workbook and grabs val | Excel Worksheet Functions | |||
Sorting and copying data to another worksheet in a workbook. | Excel Discussion (Misc queries) | |||
Copying data form website | Excel Discussion (Misc queries) | |||
copying data from one workbook to another | Excel Discussion (Misc queries) |