Save workbook multiple times as PDF with different names from range and an auto fill feature

[sgilmoreBBP]

HI all.

I'm a complete novice when it comes to VBA and Macros. I'm really hoping a whiz can save me a lot of time. I want to increase prices on a master price list but require two automated tasks in order to make my task significantly easier.
My workbook has 3 sheets.

Sheet 1 (Account Name)
has a list of customers in column A with a header in cell A1.

Sheet 2 (Current Price List)
Has the current price list with an additional column where I can input the amount that the current price will increase by (not by a percentage)

Sheet 3 (2023 Price List)
This sheet is the same as sheet 2 the only difference is that I have removed the additional column. I use a basic 'SUM' formula to now show the newly increased price.


What I'd like to achieve.
I'd like to save sheet 3 around 50 times (could be more could be less) as a PDF always in the same pre set folder, each save will have a new save name which it will pick up from the list of customers in column A in sheet 1.

Further to this, on Sheet 3 I have a cell where before I save the file to a PDF would it be possible to automatically insert the same customer name that will be used as the save name? the cell reference for this is "G3"
An example:
'customer 1' (pulled from sheet 1 cell A2) will be inserted into cell G3 on sheet 3 and then the file will be saved as PDF as "customer 1".
'customer 2' (pulled from sheet 1 cell A3) will be inserted into cell G3 on sheet 3 and then the file will be saved as PDF as "customer 2".
'customer 3' (pulled from sheet 1 cell A4) will be inserted into cell G3 on sheet 3 and then the file will be saved as PDF as "customer 3".

and so on.......




Sheet 1 (Account Name)

Book3
	A
1	Account Name
2	Customer 1
3	Customer 2
4	Customer 3
5	Customer 4
6	Customer 5
7	Customer 6
8	Customer 7
9	Customer 8
10	Customer 9
11	Customer 10
12	Customer 11
13	Customer 12
14	Customer 13
15	Customer 14
16	Customer 15
17	Customer 16
18	Customer 17
19	Customer 18
20	Customer 19
Account Name

Sheet 3 (2023 Price List)

Book3
	A	B	C	D	E	F	G	H	I	J
1
2
3						Customer:-
4						Date:-	03/10/2022
5						Validity:-	3 months from date of quote
6
7
8
9	Product Description	BBP Product Code	Shelf life upon manufacture (months unless stated otherwise)	Case Size		Cases per Layer	Layers per Pallet	Cases per Pallet	Price per unit	Price per case
10					Case Weight
11									(£)	(£)
12	Fresh Product 1	FR1	21 days	2 x (6 x 190g)	2.28kg	18	9	162	£3.80	£7.60
13	Fresh Product 2	FR2	15	60 x 40g	2.4kg	20	10	200	£4.95	£4.80
14	Fresh Product 3	FR3	15	60 x 57g	3.42kg	14	12	168	£6.15	£6.10
15	Fresh Product 4	FR4	6	10 x 1.36kg	13.60kg	(19 x 3) + 3	3+3 on top	60	£2.20	£22.00
16	Fresh Product 5	FR5	6	10 x 1.36kg	13.60kg	(19 x 3) + 3	3+3 on top	60	£2.30	£23.00
17	Fresh Product 6	FR6	6	10 x 1.36kg	13.60kg	(19 x 3) + 3	3+3 on top	60	£3.05	£30.50
18	Fresh Product 7	FR7	6	10 x 1.36kg	13.60kg	(19 x 3) + 3	3+3 on top	60	£3.40	£34.00
19	Fresh Product 8	FR8	6	12 x 454g	5.448kg	(29 x 5) + 16	5+16 on top	161	£0.95	£11.40
20	Fresh Product 9	FR9	6	12 x 334g	4.008kg	18	9	162	£1.75	£21.00
21	Fresh Product 10	FR10	6	12 x 220g	2.64kg	45	5	225	£0.77	£9.24
22	Fresh Product 11	FR11	6	12 x 220g	2.64kg	45	5	225	£1.31	£15.72
23	Fresh Product 12	FR12	6	12 x 220g	2.64kg	45	5	225	£0.82	£9.84
24	Fresh Product 13	FR13	6	12 x 220g	2.64kg	45	5	225	£0.89	£10.68
25	Fresh Product 14	FR14	6	12 x 220g	2.64kg	45	5	225	£0.97	£11.64
2023 Price List

Cell Formulas
Range	Formula
H12	H12	=F12*G12
J12	J12	=I12*2
I12:I25	I12	=SUM('[Book1]2022 Price list'!I12+'[Book1]2022 Price list'!J12)
J15:J18	J15	=I15*10
J19:J25	J19	=I19*12
G20:G25	G20	=H20/F20

 

[sgilmoreBBP]

Update.
I've watched a few videos on you tube and have put together the following module. But I'm getting the error for line "Customer = AccountName.Cells(i, l).Text
"Run-time error '424':

Object Required


This is where my lack of skill set lets me down.

Sub Save_as_PDF()

Dim data As Worksheet
Dim list As Worksheet
Dim Customer As String
Dim count As Long
Dim i As Long


Set data = ThisWorkbook.Sheets(3)
Set list = ThisWorkbook.Sheets(1)

'count number of customers
list.Activate
count = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlDown)))

data.Activate

For i = 1 To count

'Updating Customer Name
Customer = AccountName.Cells(i, 1).Text
2023 PriceList.Cells(3, 7) = Customer

'Save PDF'
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="S:\Temp\PDF save test\" & _
"Food Services - " & Customer

Next i

ActiveSheet.AutoFilterMode = False

End Sub[/CODE][/CODE]

 

[mumps]

Try:

Sub CopySheetPDF()
    Application.ScreenUpdating = False
    Dim custWS As Worksheet, cust As Range
    Set custWS = Sheets("Sheet1")
    With custWS
        For Each cust In .Range("A2", .Range("A" & .Rows.Count).End(xlUp))
            Sheets("Sheet3").Copy
            With ActiveSheet
                .Range("G3") = cust
                .ExportAsFixedFormat Type:=xlTypePDF, Filename:="S:\Temp\PDF save test\" & "Food Services - " & cust
            End With
        Next cust
    End With
    Application.ScreenUpdating = True
End Sub

 

[sgilmoreBBP]

Hey @mumps Thank you for replying. I sorted it in the end after lots of googling. In the end this is what I used:

Sub Save_as_PDF()

Dim data As Worksheet
Dim list As Worksheet
Dim Customer As String
Dim count As Long
Dim i As Long


Set data = ThisWorkbook.Sheets(3)
Set list = ThisWorkbook.Sheets(1)

'count number of customers
list.Activate
count = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlDown)))

data.Activate

For i = 1 To count

'Updating Customer Name
Customer = Worksheets("AccountName").Cells(i, 1)
Worksheets("NewPriceList").Cells(3, 7) = Customer

'Save PDF'
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="S:\Temp\PDF save test\" & _
"Food Services - " & Customer

Next i

ActiveSheet.AutoFilterMode = False

End Sub

 

[mumps]

Did you try my macro?