How would I go about this?

[sabbiee]

I have data from a program I export into a .cvs file. I have created a macro to almost get it organized how I want it to be. I then need to get data from that sheet to another worksheet.

With the data, I am trying to only leave the rows that contain today's date. And then I need to get the first and last name transferred to the Name section on my template and the insurance to the insurance section on the template. I cannot get excel to only leave the rows with today's date. I've tried various macros I have found online. And as far as the template, do I have to map it or something?

 

[Snakehips]

@sabbiee Welcome.
Something along these lines.
It assumes that Name is in Template column B and Insurance in column H
And that the template 'form' starts with first Name in row 2 and then repeats every 10 rows?
If those assumptions are wrong then it will need adapting.

This code in the Data sheet module.

Sub Fill_Template()
Dim lr As Long
Dim r As Long
Dim Rng As Range
'Dim cell As Range

Dim Temp As Worksheet

'find last row data
lr = Range("A" & Rows.Count).End(xlUp).Row
'reduce data to today date only
Application.ScreenUpdating = False
For r = lr To 2 Step -1

    If Not Int(Cells(r, 3)) = Date Then
        Cells(r, 3).EntireRow.Delete
        lr = lr - 1
    End If
Next r

'Fill template
'Name row repeats every x rows?  (assume 10 ?)  Name in B, Insurance in  H ?
x = 10
For r = 2 To lr
    tr = 2 + (x * (r - 2))
    Sheets("Template").Range("B" & tr) = Range("A" & r) & "  " & Range("B" & r)
    Sheets("Template").Range("H" & tr) = Range("D" & r)
Next
Application.ScreenUpdating = True

End Sub

Starting data:

Book2
	A	B	C	D
1	First Name	Last name	Next Appointment	Primary insurance Details
2	John	Smith	6/9/22 9:45	High Mark Blue Shield
3	Tom	Thumb	6/8/22 17:15	American Speciality Health
4	Jane	Doe	6/8/22 16:45	American Speciality Health
5	Charlie	Farley	6/14/22 8:30	Self Pay
6	Who	Mi	6/14/22 8:30	Self Pay
7
Data

Resultant data:

Book2
	A	B	C	D
1	First Name	Last name	Next Appointment	Primary insurance Details
2	Tom	Thumb	6/8/22 17:15	American Speciality Health
3	Jane	Doe	6/8/22 16:45	American Speciality Health
4
Data

Template:

Book2
	A	B	C	D	E	F	G	H	I	J
1
2	Name:	Tom Thumb					Insurance:	American Speciality Health
3
4
5
6
7
8
9
10
11
12	Name:	Jane Doe					Insurance:	American Speciality Health
13
14
15
16
17
18
19
20
21
22	Name:						Insurance:
23
24
Template

Hope that helps.

 

[sabbiee]

Oh wow that you so much for the explanation!
My code just to sort the data how I wanted it was super long! But it worked to do what I was attempting lol.
Sub Macro1()
'
' Appointments Macro
'

'
Cells.Select
Cells.EntireColumn.AutoFit
Columns("C:C").Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add2 Key:=Range("C1:C3984") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A1:E3984")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Sub Macro2()
Columns("C").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

Sub Macro3()
Dim LR As Long, i As Long
LR = Range("C" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
If Range("C" & i).Value > Date + 1 Then Rows(i).Delete
Next i
End Sub
Sub Macro4()
'
' Macro4 Macro
'

'
Columns("C:C").Select
Selection.NumberFormat = "[$-en-US]m/d/yy h:mm AM/PM;@"
End Sub
Sub Macro5()
'
' Macro5 Macro
'

'
Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
End Sub


Sub CombineCodes()
Macro1
Macro2
Macro4
Macro3
Macro5
End Sub

 

[sabbiee]

@sabbiee Welcome.
Something along these lines.
It assumes that Name is in Template column B and Insurance in column H
And that the template 'form' starts with first Name in row 2 and then repeats every 10 rows?
If those assumptions are wrong then it will need adapting.

This code in the Data sheet module.

Sub Fill_Template()
Dim lr As Long
Dim r As Long
Dim Rng As Range
'Dim cell As Range

Dim Temp As Worksheet

'find last row data
lr = Range("A" & Rows.Count).End(xlUp).Row
'reduce data to today date only
Application.ScreenUpdating = False
For r = lr To 2 Step -1

    If Not Int(Cells(r, 3)) = Date Then
        Cells(r, 3).EntireRow.Delete
        lr = lr - 1
    End If
Next r

'Fill template
'Name row repeats every x rows?  (assume 10 ?)  Name in B, Insurance in  H ?
x = 10
For r = 2 To lr
    tr = 2 + (x * (r - 2))
    Sheets("Template").Range("B" & tr) = Range("A" & r) & "  " & Range("B" & r)
    Sheets("Template").Range("H" & tr) = Range("D" & r)
Next
Application.ScreenUpdating = True

End Sub

Starting data:

Book2
	A	B	C	D
1	First Name	Last name	Next Appointment	Primary insurance Details
2	John	Smith	6/9/22 9:45	High Mark Blue Shield
3	Tom	Thumb	6/8/22 17:15	American Speciality Health
4	Jane	Doe	6/8/22 16:45	American Speciality Health
5	Charlie	Farley	6/14/22 8:30	Self Pay
6	Who	Mi	6/14/22 8:30	Self Pay
7
Data

Resultant data:

Book2
	A	B	C	D
1	First Name	Last name	Next Appointment	Primary insurance Details
2	Tom	Thumb	6/8/22 17:15	American Speciality Health
3	Jane	Doe	6/8/22 16:45	American Speciality Health
4
Data

Template:

Book2
	A	B	C	D	E	F	G	H	I	J
1
2	Name:	Tom Thumb					Insurance:	American Speciality Health
3
4
5
6
7
8
9
10
11
12	Name:	Jane Doe					Insurance:	American Speciality Health
13
14
15
16
17
18
19
20
21
22	Name:						Insurance:
23
24
Template

Hope that helps.

So as far as executing this,
When I download the information I need from the program I get it from as the .csv, I save it as Info.csv. I would then run the macro under that file? Do I need a separate sheet for the template generating?

 

[Snakehips]

My code is expecting that the data sheet and template sheet are in the same workbook, with the code in the Data sheet's code module.
Might this be the way for you to go?
Create a workbook that has a Data sheet and Template sheet. (Maybe need to open a copy each time ?)
Have the code in the Data sheet module.
Open data csv in Excel.
Copy / Paste into the Data sheet
Run the code to extract names to Template.