change VBA code so i can rename folder path from cell A1

[shili12]

I want to have a simpler way of renaming folder path, maybe one can type into cell A1 the name of folder to import and the code changes,
here folderPath = "C:\Users\S.Lakh\Downloads\allied\"

Sub ImportWorkbooks()
    Dim folderPath As String
    Dim fileName As String
    Dim wbTarget As Workbook
    Dim wbSource As Workbook
    Dim wsTarget As Worksheet
    Dim wsSource As Worksheet
    Dim lastRow As Long
    
    ' Set the folder path
    folderPath = "C:\Users\S.Lakh\Downloads\allied\"
    
    ' Create a new workbook to consolidate the data
    Set wbTarget = Workbooks.Add
    Set wsTarget = wbTarget.Sheets(1)
    
    ' Loop through all files in the folder
    fileName = Dir(folderPath & "*.xlsx")
    Do While fileName <> ""
        ' Open each workbook in the folder
        Set wbSource = Workbooks.Open(folderPath & fileName)
        Set wsSource = wbSource.Sheets(1)
        
        ' Find the last row in the target workbook
        lastRow = wsTarget.Cells(wsTarget.Rows.Count, 1).End(xlUp).Row
        
        ' Copy data from source workbook to target workbook
        wsSource.UsedRange.Copy wsTarget.Cells(lastRow + 1, 1)
        
        ' Close the source workbook
        wbSource.Close False
        
        ' Get the next file in the folder
        fileName = Dir
    Loop
    
    ' Save and close the target workbook
    wbTarget.SaveAs folderPath & "Consolidated_Workbooks.xlsx"
    wbTarget.Close False
    
    MsgBox "Workbooks have been consolidated successfully!", vbInformation
End Sub

 

[Belleke]

folderPath = [A1].Value

And In cell A1 path
C:\Users\S.Lakh\Downloads\allied\ (without the brackets)

 

[Marc L]

How to choose a folder via a VBA double click event on a specific cell :​

Move all files in folder to another directory folder

Hi, See file attached. Need help to modify the program. There are 2 directories folder one is 'copy from' and other is 'copy to' and this program work only if you know the directory and paste in these cell C3 and C5 and click the button 'move files' to run. I want to modify the VBA (excel...

chandoo.org