Macro to fill cell(s) on one sheet from a list on another sheet

[muhleebbin]

Is there an easy macro to fill cells on one sheet from the cells on a second sheet?

Below is Sheet1's page that needs to fill in B4 (Subscriber Name) and B7 (Subscriber ID#). The list can be found in columns A & B on Sheet2 and there's 49 entries.

temp ID template.xlsx
	A	B
3	All Savers
4	Subscriber Name
5	Group Name
6	Group #
7	Subscriber ID#
8	Plan
9	Rx Bin #
10	Rx PCN
11	Rx Group
12
13	Claims Address	All Savers
14	Address	PO Box 31375
15		Salt Lake City, UT 84131
16	Pharmacy phone #	800-797-9791
17	Customer Service phone #	800-291-2634
Sheet1

Ultimately i'd like it to save as a pdf of each individual with their name as the file name.

 

[maabadi]

1. Please show your another sheet also
2. where is your criteria?

 

[maabadi]

If your sheet2 like as uploaded image Then You can use this macro.
1. Change first part of save location to location you want.
2. if you set rng for print , Change 2 lines after Savelocation with 4 lines after them (add one ' to first 2 lines and remove all ' existed before at code.

Sub SaveListPDF()
Dim i As Long
Dim Lastrow As Long
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim saveLocation As String
Dim FN As String
'Dim rng As Range

Set WS1 = Worksheets("Sheet1")
Set WS2 = Worksheets("Sheet2")

Lastrow = WS2.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To Lastrow
FN = WS2.Range("A" & i).Value
WS1.Range("B2").Value = WS2.Range("A" & i).Value
WS1.Range("B3").Value = WS2.Range("C" & i).Value
WS1.Range("B4").Value = WS2.Range("D" & i).Value
WS1.Range("B5").Value = WS2.Range("B" & i).Value
WS1.Range("B6").Value = WS2.Range("E" & i).Value
WS1.Range("B7").Value = WS2.Range("F" & i).Value
WS1.Range("B8").Value = WS2.Range("G" & i).Value
WS1.Range("B9").Value = WS2.Range("H" & i).Value
saveLocation = "C:\Users\MMMM\OneDrive\Documents\" & FN & ".pdf"
WS1.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveLocation, Quality:=xlQualityStandard, _
IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False
'Set rng = WS1.Range("A1:B" & WS1.Cells(.Rows.Count, 1).End(xlUp).Row)
'rng.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveLocation, Quality:=xlQualityStandard, _
  IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False
Next
End Sub

 

[muhleebbin]

Maabadi, below is an example of data

temp ID template.xlsx
	A	B
1	Member Name	Member ID
2	Test 1	1000001
3	Test 2	1000002
4	Test 3	1000003
5	Test 4	1000004
6	Test 5	1000005
7	Test 6	1000006
8	Test 7	1000007
9	Test 8	1000008
10	Test 9	1000009
11	Test 10	1000010
12	Test 11	1000011
13	Test 12	1000012
14	Test 13	1000013
15	Test 14	1000014
16	Test 15	1000015
17	Test 16	1000016
18	Test 17	1000017
19	Test 18	1000018
20	Test 19	1000019
21	Test 20	1000020
22	Test 21	1000021
23	Test 22	1000022
24	Test 23	1000023
25	Test 24	1000024
26	Test 25	1000025
27	Test 26	1000026
28	Test 27	1000027
29	Test 28	1000028
30	Test 29	1000029
31	Test 30	1000030
32	Test 31	1000031
33	Test 32	1000032
34	Test 33	1000033
35	Test 34	1000034
36	Test 35	1000035
37	Test 36	1000036
38	Test 37	1000037
39	Test 38	1000038
40	Test 39	1000039
41	Test 40	1000040
42	Test 41	1000041
43	Test 42	1000042
44	Test 43	1000043
45	Test 44	1000044
46	Test 45	1000045
47	Test 46	1000046
48	Test 47	1000047
49	Test 48	1000048
50	Test 49	1000049
Sheet2

But i'm actually ok with the version that you proposed because eventually we'll have multiple options for our health insurance which will require the other fields to change. Currently only B4 and B7 are the fields that would be different from employee to employee.

Regarding this portion of your code:

WS1.Range("B2").Value = WS2.Range("A" & i).Value
WS1.Range("B3").Value = WS2.Range("C" & i).Value
WS1.Range("B4").Value = WS2.Range("D" & i).Value
WS1.Range("B5").Value = WS2.Range("B" & i).Value
WS1.Range("B6").Value = WS2.Range("E" & i).Value
WS1.Range("B7").Value = WS2.Range("F" & i).Value
WS1.Range("B8").Value = WS2.Range("G" & i).Value
WS1.Range("B9").Value = WS2.Range("H" & i).Value

Should it be B4-B11 in the WS1 Range to fill in the appropriate cells in the above capture?

 

[maabadi]

Yes. Change B2 to B4 and ... to the last.
if you don't need others then add one ' at the first of line or delete it. Then:

Sub SaveListPDF()
Dim i As Long
Dim Lastrow As Long
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim saveLocation As String
Dim FN As String
'Dim rng As Range

Set WS1 = Worksheets("Sheet1")
Set WS2 = Worksheets("Sheet2")

Lastrow = WS2.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To Lastrow
FN = WS2.Range("A" & i).Value
WS1.Range("B4").Value = WS2.Range("A" & i).Value
'WS1.Range("B5").Value = WS2.Range("C" & i).Value
'WS1.Range("B6").Value = WS2.Range("D" & i).Value
WS1.Range("B7").Value = WS2.Range("B" & i).Value
'WS1.Range("B8").Value = WS2.Range("E" & i).Value
'WS1.Range("B9").Value = WS2.Range("F" & i).Value
'WS1.Range("B10").Value = WS2.Range("G" & i).Value
'WS1.Range("B11").Value = WS2.Range("H" & i).Value
saveLocation = "C:\Users\MMMM\OneDrive\Documents\" & FN & ".pdf"
WS1.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveLocation, Quality:=xlQualityStandard, _
IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False
'Set rng = WS1.Range("A1:B" & WS1.Cells(.Rows.Count, 1).End(xlUp).Row)
'rng.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveLocation, Quality:=xlQualityStandard, _
  IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False
Next
End Sub

 

[muhleebbin]

Thanks Maabadi, that worked perfectly!

 

[muhleebbin]

Just out of curiosity, the ' before a line of code basically tells it to ignore that particular line?

 

[maabadi]

Yes. it remove that Line from running. and if you want added it later to code, only remove ' from first of line.

 

[muhleebbin]

Thanks for the knowledge!
I've run into codes where I did need them in one instance but not another and then vice versa, this helps make the code easier to copy and paste.