Change folder path location - dynamic - so i can choose or pick multiple excel files

ajayfbd

New Member
Joined
Jul 18, 2020
Messages
4
Office Version
  1. 2010
Platform
  1. Windows
Hello everyone - i have found below VBA code for Copy data from multiple workbooks in this forum which is perfectly ok
now i just want to change folder path because my folder is not on same location
please help me to change folder path location - dynamic - so i can choose or pick multiple excel files
Rich (BB code):
Sub CopyRange()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Set wkbDest = ThisWorkbook
    Dim LastRow As Long
  Const strPath As String = " C:\Users\xbv\Desktop\group1\"
    ChDir strPath
    strExtension = Dir("*.xls*")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With wkbSource
            LastRow = .Sheets("appendix B").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Sheets("appendix B").Range("C6:F" & LastRow).Copy wkbDest.Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            .Close savechanges:=False
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

daverunt

Well-known Member
Joined
Jul 9, 2009
Messages
1,891
Office Version
  1. 2013
Platform
  1. Windows
Hi

I use the following to select multiple .csv files in a folder of my choosing. You can change the extn.

Code:
'Display Open Dialog to select file directory
filenames = Application.GetOpenFilename("Excel Files (*.csv*)," & _
"*.csv*", 1, "select csv files", "Open", True)

'If the user Cancels file selection then exit
If TypeName(filenames) = "Boolean" Then
Exit Sub
End If

   For i = 1 To UBound(filenames)

   Workbooks.Open filenames(i)
   Set CSVFile = ActiveWorkbook

   ~~~~~~


   Next i
 

ajayfbd

New Member
Joined
Jul 18, 2020
Messages
4
Office Version
  1. 2010
Platform
  1. Windows
dear sir, how can i use this code with my above code
 

ajayfbd

New Member
Joined
Jul 18, 2020
Messages
4
Office Version
  1. 2010
Platform
  1. Windows
i need to copy paste data specific range from multiple workbook in to a master workbook, please refer my code for details
 

daverunt

Well-known Member
Joined
Jul 9, 2009
Messages
1,891
Office Version
  1. 2013
Platform
  1. Windows
Sorry. Been away.
Try the following. It should process the files you select.

Code:
Sub CopyRange()
Application.ScreenUpdating = False
Dim wkbDest As Workbook
Dim wkbSource As Workbook
Set wkbDest = ThisWorkbook
Dim LastRow As Long


filenames = Application.GetOpenFilename("Excel Files (*.xls*)," & _
"*.xls*", 1, "select xls files", "Open", True)

'If the user Cancels file selection then exit
If TypeName(filenames) = "Boolean" Then
Exit Sub
    End If

   For i = 1 To UBound(filenames)

   Set wkbSource = Workbooks.Open(filenames(i))

    With wkbSource
            LastRow = .Sheets("appendix B").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Sheets("appendix B").Range("C6:F" & LastRow).Copy wkbDest.Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            .Close savechanges:=False
      End With


Next i
Application.ScreenUpdating = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,126,939
Messages
5,621,717
Members
415,853
Latest member
Newlife72

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