using forms to add data to multiple worksheets

[Andy]

I am trying to enter numbers for surgeries that several doctors I work
for complete. I would like to track each surgeon's numbers on separate
worksheets but use one central page to input all the data on. I would
like to use the 1st page of the workbook to allow the user to enter the
number of surgeries (via forms) and then have excel paste that
information just entered onto a worksheet specifically for that surgeon
- requiring 3 separate worksheets, plus the 1st for the form. Can this
be done?

Thanks in advance -

Andy

 

[Jim Gordon MVP]

Hi Andy,

There are lots of ways, from simple to complex, to accomplish this task.

For simplicity, you can make a very simple macro by turning on the macro
recorder on the Tools menu. You can assign macros to buttons, shapes,
just about any object. The macros you make can copy the information from
one sheet to another (hint: try using Paste Special > Values).

-Jim

 

[Andy]

Hello Jim -
I tried using a macro but here is what is happening: I use the form
function to enter the data and then use the macro to copy that
information, paste it into a new worksheet and then erase the original
input data from the 1st page. Now when inputing a second set of data,
the macro pastes the new information on top of the previous entry. I
am not getting a new list of information, only the last entered set.
And is there a way to keep the form data entry box on the screen? The
people I am writing this for need as few directions such as "Click on
DATA on the toolbar and then click FORM . . ."

Andy

 

[Jim Gordon MVP]

Hi,

Could you post your macro here so we can see what it is doing?

Thanks.

 

[Andy]

No problem - here ya go! What I was trying to do was copy the 5 datum
entered using forms (cell range B3:G3) (worsheet is named "data entry")
and then paste it into the next worksheet named "SSL". I then erased
the line of data entered on the 1st page.

Sub datapaste()
'
' datapaste Macro
' Macro recorded 1/15/2006 by Daniel Winters
'

'
Range("B3:G3").Select
Selection.Copy
Sheets("SSL").Select
Range("B3:G3").Select
ActiveSheet.Paste
Sheets("Data Entry").Select
Range("B3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "1/14/006"
Range("B3").Select
ActiveCell.FormulaR1C1 = ""
Range("C3").Select
ActiveCell.FormulaR1C1 = ""
Range("D3").Select
ActiveCell.FormulaR1C1 = ""
Range("E3").Select
ActiveCell.FormulaR1C1 = ""
Range("F3").Select
ActiveCell.FormulaR1C1 = ""
Range("G3").Select
ActiveCell.FormulaR1C1 = ""
End Sub
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 1/15/2006 by Daniel Winters
'

'
Range("B5:G5").Select
Selection.Copy
Sheets("SSL").Select
Range("B3:G3").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=False
Sheets("Data Entry").Select
Application.CutCopyMode = False
Selection.Clear
End Sub

Any help would be appreciated!

Andy