Hello All,

I have annotated the below code as I believe it might be a better explanation as to what I would like my file to do.

Sub Macro1()

Dim array_sheets As Variant

'The sheets that I need the information copied from

array_sheets = Array("ABS", "SCS", "CUS", "FIN", "H&R", "NET", "CEO", "ROP", "S&E")

    
'This needs to loop through the above array

    Sheets("ABS").Select
    
'This range will always start from cells A2 and B2 (then A3 and B3 etc)
'but will vary in length and need it to stop when it reaches the last
'values in column A as column B doesnt always have data
    
    Range("A2:B2").Select
    Application.CutCopyMode = False
    Selection.Copy
    
'This is the destination cell that i need the range pasted and transposed to this will never change
    
    Sheets("Measure Overview").Select
    Range("S2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
 
'Each time the above copy paste transpose is done i need the below PDF code to run
 
    Dim pack_name As String
    Dim pack_array As Variant
    Dim file_prefix As String
    Dim file_suffix As String
    
    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
    
    Sheets("Main").Select
 
'In total this macro should produce roughly 150 PDF's
 
End Sub
Thanks in advance.