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.
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;
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
So close! Script creates worksheet and populates BUT