While Consolidating the Data from different workbooks, Required Folder Selection as well

Balakrishnan2027

New Member
Joined
Mar 1, 2021
Messages
4
Office Version
  1. 365
I am currently using the below code, to consolidate the data from diffrent workbooks. In this instead of standard path, I need to change the path to dynamic. Pls help

VBA Code:
Sub CopyRange()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Set wkbDest = ThisWorkbook
    Dim LastRow As Long
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Master")
    Const strPath As String = "C:\Users\1530959\Desktop\Bala\Macro\"
    ChDir strPath
   
    'clear the existting values in summary sheet
    Sheets("Master").Select
    Rows("2:500000").Select
    Selection.ClearContents
    Application.DisplayAlerts = False

    strExtension = Dir("*.xlsx*")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With wkbSource
            LastRow = .Sheets("Consol").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Sheets("Consol").Range("A2:D" & LastRow).Copy wkbDest.Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            .Close savechanges:=False
    End With
    strExtension = Dir
    Loop
   
    ws.Activate
    On Error Resume Next
        ws.ShowAllData
    On Error GoTo 0
   
    ws.Range("A2:A500000").AutoFilter Field:=1, Criteria1:="#VALUE"
    Application.DisplayAlerts = False
        ws.Range("A2:A500000").SpecialCells(xlCellTypeVisible).Delete
    Application.DisplayAlerts = True
    On Error Resume Next
        ws.ShowAllData
    On Error GoTo 0
   
   
    Application.ScreenUpdating = True
End Sub
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows
In what way does it need to be dynamic?
 

Balakrishnan2027

New Member
Joined
Mar 1, 2021
Messages
4
Office Version
  1. 365
Currently it was taking the files from the path "C:\Users\Desktop\Bala\Macro\". Instead, I want to select the folder every time.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows
Ok how about
VBA Code:
    Set ws = ThisWorkbook.Worksheets("Master")
    Dim strPath As String
    
    With Application.FileDialog(4)
      .AllowMultiSelect = False
      If .Show Then strPath = .SelectedItems(1)
   End With
      
   If strPath = "" Then Exit Sub
   strPath = strPath & Application.PathSeparator
      
   
    'clear the existting values in summary sheet
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows
In what way?
 

Watch MrExcel Video

Forum statistics

Threads
1,126,957
Messages
5,621,823
Members
415,859
Latest member
Vain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top