VBA Please help me fix and improve the code.

[punnipah]

Hi

I want to edit 3 places of code.
1.Set ws = Workbooks.Open(reportFolder & "Report_for_SC.xlsx").Sheets(1) : I don't want to specify names. Report_for_SC.xlsx Because sometimes it won't be this name, but it will have the word Report every time.
2.pdfFileName = pdfFolder & "Inv SC 12.2023\" & reportValue & "_2023.pdf" : I don't want to specify the pdfFolder names "Inv SC 12.2023 and "2023.pdf", but it always contains "Inv" and .PDF.
3.newPdfFileName = pdfFolder & "Inv SC 12.2023\" & reportValue & "_" & copyValue & ".pdf" : I don't want to specify the newPdfFileName names "Inv SC 12.2023" but it always contains "Inv" and .PDF.

Sub CompareFilesAndCopy()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim reportFolder As String
    Dim pdfFolder As String
    Dim reportValue As String
    Dim pdfFileName As String
    Dim copyValue As Variant
    Dim newPdfFileName As String
    Dim existingPdfFileName As String
 
    Dim fileNamePattern As String
    Dim file As String
  
  

    reportFolder = "D:\FBB Edit FileName\Macro\Input\"
  
    fileNamePattern = "Report"
 
    file = Dir(reportFolder & fileNamePattern)

    Do While file <> ""

        'MsgBox "Found file: " & reportFolder & file
      
 
        file = Dir
    Loop
  

    pdfFolder = "D:\FBB Edit FileName\Macro\Output\"
  

    Set ws = Workbooks.Open(reportFolder & "Report_for_SC.xlsx").Sheets(1)

    Set rng = ws.Range("H2:H" & ws.Cells(ws.Rows.Count, "H").End(xlUp).row)
  

    For Each cell In rng

        reportValue = cell.Value
      

        pdfFileName = pdfFolder & "Inv SC 12.2023\" & reportValue & "_2023.pdf"
      
   
        If Dir(pdfFileName) <> "" Then
   
            copyValue = ws.Range("C" & cell.row).Value
          
    
            newPdfFileName = pdfFolder & "Inv SC 12.2023\" & reportValue & "_" & copyValue & ".pdf"
          
    
            FileCopy pdfFileName, newPdfFileName
 
            existingPdfFileName = pdfFolder & "Inv SC 12.2023\" & reportValue & "_2023.pdf"
            If Dir(existingPdfFileName) <> "" Then
                Kill existingPdfFileName
            End If
          

            Dim copyPdfFileName As String
            copyPdfFileName = pdfFolder & "Inv SC 12.2023\" & reportValue & "_2023(copy).pdf"
            If Dir(copyPdfFileName) <> "" Then
                Kill copyPdfFileName
            End If
        Else
 
 
            'MsgBox "àÅ¢·Õè " & reportValue & " äÁ辺ã¹ä¿Åì PDF", vbInformation
        End If
    Next cell
  

    ws.Parent.Close False
End Sub

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy, and your thread more likely to attract helpers. My signature block below has more details. I have added the tags for you this time.

 

[punnipah]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy, and your thread more likely to attract helpers. My signature block below has more details. I have added the tags for you this time.

Please help me