Like so, but I get an error still because "report_pages" is not a filled variable, I assume you have the done another way:
Sub Macro1()
Dim shARR As Variant, i As Long, LR As Long
Dim pack_array As Variant, pack_name As String, file_prefix As String, file_suffix As String
'The sheets that I need the information copied from
shARR = Array("ABS", "SCS", "CUS", "FIN", "H&R", "NET", "CEO", "ROP", "S&E")
'This needs to loop through the above array
For i = LBound(shARR) To UBound(shARR)
With Sheets(shARR(i))
'get last row of data by looking UP column A
LR = .Range("A" & .Rows.Count).End(xlUp).Row
'Copy the data
.Range("A2:B" & LR).Copy
'This is the destination cell that i need the range pasted and transposed to this will never change
Sheets("Measure Overview").Range("S2").PasteSpecial xlPasteValues, Transpose:=True
'Each time the above copy paste transpose is done i need the below PDF code to run
file_prefix = (ThisWorkbook.Path & "\")
file_suffix = (" " & Format(Now, "mm-yyyy") & ".pdf")
pack_name = Sheets("Main").Range("C5").Value
pack_array = report_pages
Sheets(pack_array).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=file_prefix & pack_name & file_suffix, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
Next i
End Sub
Bookmarks