Hello, I have a current macro that opens a user box you fill out 3 fields then y

Question

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 user box have been filled out you click on next patient and the info that was entered in to the user box will go to the main worksheet and 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 from the beginning. This time 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.

Any suggestion???

Explanation / Answer

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 real date. Textboxes return 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 Long
'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 Sub

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

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