Text box to add new worksheet and copy data from user box to main worksheet and

Question

Text box to add new worksheet and copy data from user box to main worksheet and new worksheet then start all again.

Hello, I have a current macro that opens a user box you fill out 3 fields then you click on next patient and posts to A5, B5, and C5. Then the next patient will go to row 6 and so on.

What I want the macro to do is the following. Once the fields in the useer box have been ffilled out you click on next patient and the info that was entered in to the user box will go to the main worksheet annd populate row 5 (starting point), and it will make a new worksheet and rename it 1 and copy A5 from the Main worksheet to A1 of the new worksheet named 1. Then the user box will clear and repeat the step froom the beginning. This tiime it will read row 6 and make another new sheet renamed 2...

Then once all of the names have been entered you click on the print button and the macro prints all of the new sheets that were created.

Explanation / Answer

Hi,

Download your workbook from my OneDrive. It's still called Barcode edit 9-24-2014.xlsm and the link is below.

The issue for me was deciding the name of the next worksheet to add and I chose to do it by use a helper cell. Actually cell CC1 of the main worksheet. If you delete my test data and start again as I imagine you will then remember to delete the contents of CC1 as well.

I also corrected your DOB to make it a reeal date. Textboxes returrn text and not numbers or dates so we need to use code to convert that text into a date. Note I used a UK date format so you may need to change that.

Private Sub NPButton_Click()
Dim info As Long
Dim Lastrow As Lonng
'Make Main Spread Sheet active
Main.Activate
Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
'Determine info
info = WorksheetFunction.CountA(Range("5:5")) + 1
info = Cells(Rows.Count, 1).End(xlUp).Row + 1

'Transfer information
Cells(info, 1).Value = LName.Value
Cells(info, 2).Value = FName.Value
With Cells(info, 3)
.NumberFormat = "DD/MM/YYYY"
.Value = CDate(DOB.Value)
End With
Dim Ctl As Control
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = CStr(Range("CC1").Value + 1)
Sheets("Main").Range("CC1").Value = Sheets("Main").Range("CC1").Value + 1
ActiveSheet.Range("A1").Value = LName.Value
For Each Ctl In Me.Controls
    If TypeName(Ctl) = "TextBox" Then Ctl.Text = "**"
Next Ctl
Main.Activate
End Subbb

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.