kansfan2001
New Member
- Joined
- Aug 22, 2020
- Messages
- 21
- Office Version
- 365
- Platform
- Windows
I have built a code to import specific areas from one sheet and placing that data into another. But for the life of me I can not figure out how to get it to repeat for all the excel files in the folder. I have to manually add each folder. I was wondering how to make the VBA below function for all file in the folder before it stops?
Thanks for the help its greatly appreciated
Thanks for the help its greatly appreciated
VBA Code:
Sub ImportFromPickSheet()
'
' ImportFromPickSheet Macro
'
'
' Defines variables Workbook 1 is for the MNF Calculator - Workbook 2 is for the Player Pick Sheet
Dim wb1 As Workbook, wb2 As Workbook
' Disable screen updating to reduce screen flicker
Application.ScreenUpdating = False
' Define which workbook is which (Change "C:\" to pick import sheet location)
Set wb1 = ThisWorkbook
ChDir "C:\"
FName = Application.GetOpenFilename
If FName <> False Then
Set wb2 = Workbooks.Open(FName)
End If
' Copy range B6:AM5 from ImportToCalc (Sheet2) of Workbook 2 and Import into ImportFromPickSheet (Sheet3) of Workbook 1
With wb2.Sheets("ImportToCalc").Range("B5:AM5")
wb1.Sheets("ImportFromPickSheet").Range("B" & Rows.Count).End(xlUp)(2).Resize(.Rows.Count, .Columns.Count).Value2 = .Value2
End With
' Close wb2
wb2.Close
' Re-enable screen updating
Application.ScreenUpdating = True
End Sub