So close! Script creates worksheet and populates BUT [Closed]

Report
Posts
1
Registration date
Friday July 1, 2016
Status
Member
Last seen
July 1, 2016
-
Over the last 10 hours I have researched and pondered and really enjoyed the process of building up this program, but my shift is almost over and I won't be able to work on it until tomorrow night D; so I'm hoping someone here will be able to guide me in the right direction!

This script pulls data from 2 existing worksheets. 1Names has a list of names (B Column), dates of birth (D Column), and allergies (E Column), sorted into columns accordingly. It takes those values and stores them into ranges, and then creates a new sheet from the 2nd worksheet in the workbook called MARS, naming the sheet according to the current value of ClientName. Then it populates the NEW MARS sheet (which is named according to ClientName) with name, DoB, allergies in the correct positions. Then it loops until it runs out of names to use.

THE PROBLEM!
It works perfectly for the first name on the list, and then when it loops it screws up the data which it's pulling from 1Names... It'll change the value of ClientDOB and ClientAllergies to the value of ClientName instead of the values which they are supposed to be. Why is this?! I cannot figure it out D;

Sub createNewSheet()

Dim wsNew As Worksheet
Dim wsData As Worksheet

'Rename this sheet to the name of the sheet where your data is located
Set wsData = ActiveWorkbook.Sheets("1Names")

With wsData
Dim rng As Range
Dim rngDoB As Range
Dim rngAllergies As Range
Dim ClientName As String
Dim ClientDOB As String

Set rngDoB = .Range("D2")
Set rngAllergies = .Range("E2")
Set rng = .Range("B2")
Do Until IsEmpty(rng)
Sheets("2MARS").Copy After:=Sheets(Sheets.Count)
With rng
Sheets(ActiveSheet.Name).Name = rng
End With
ClientName = rng.Value
ClientDOB = rngDoB.Value
ClientAllergies = rngAllergies.Value
ActiveSheet.Range("F26").Value = ClientName
ActiveSheet.Range("AB26").Value = ClientDOB
ActiveSheet.Range("S1").Value = ClientAllergies
Set rng = rng.Offset(1, 0)
Set rngDoB = rng.Offset(1, 0)
Set rngAllergies = rng.Offset(1, 0)
Loop
End With

Set wsNew = ActiveWorkbook.ActiveSheet


End Sub

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!