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

muhleebbin

Board Regular
Joined
Sep 30, 2017
Messages
97
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
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
AB
3All Savers
4Subscriber Name
5Group Name
6Group #
7Subscriber ID#
8Plan
9Rx Bin #
10Rx PCN
11Rx Group
12
13Claims AddressAll Savers
14AddressPO Box 31375
15Salt Lake City, UT 84131
16Pharmacy phone #800-797-9791
17Customer 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.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,642
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
1. Please show your another sheet also
2. where is your criteria?
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,642
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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.
VBA 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
 

Attachments

  • 1234.jpg
    1234.jpg
    32.5 KB · Views: 0

muhleebbin

Board Regular
Joined
Sep 30, 2017
Messages
97
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
Maabadi, below is an example of data

temp ID template.xlsx
AB
1Member NameMember ID
2Test 11000001
3Test 21000002
4Test 31000003
5Test 41000004
6Test 51000005
7Test 61000006
8Test 71000007
9Test 81000008
10Test 91000009
11Test 101000010
12Test 111000011
13Test 121000012
14Test 131000013
15Test 141000014
16Test 151000015
17Test 161000016
18Test 171000017
19Test 181000018
20Test 191000019
21Test 201000020
22Test 211000021
23Test 221000022
24Test 231000023
25Test 241000024
26Test 251000025
27Test 261000026
28Test 271000027
29Test 281000028
30Test 291000029
31Test 301000030
32Test 311000031
33Test 321000032
34Test 331000033
35Test 341000034
36Test 351000035
37Test 361000036
38Test 371000037
39Test 381000038
40Test 391000039
41Test 401000040
42Test 411000041
43Test 421000042
44Test 431000043
45Test 441000044
46Test 451000045
47Test 461000046
48Test 471000047
49Test 481000048
50Test 491000049
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

Well-known Member
Joined
Oct 22, 2012
Messages
1,642
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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:

VBA 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("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
 
Solution

muhleebbin

Board Regular
Joined
Sep 30, 2017
Messages
97
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
Thanks Maabadi, that worked perfectly!
 

muhleebbin

Board Regular
Joined
Sep 30, 2017
Messages
97
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web

ADVERTISEMENT

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

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,642
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Yes. it remove that Line from running. and if you want added it later to code, only remove ' from first of line.
 

muhleebbin

Board Regular
Joined
Sep 30, 2017
Messages
97
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,742
Messages
5,626,618
Members
416,195
Latest member
tonmcg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top