Running macro below from Excel-Easy, I get following error:
Run-time error '1004': Excel cannot access 'SalesReports'. The document may be read-only or encrypted.
What I’m trying to do:
Copy sheets from a bunch of external workbooks into a new workbook I’ve created. Each external workbook contains only one sheet—the workbook & sheet names are variable.
The externals are in folder D:\BB\SalesReports, my new workbook is elsewhere—but same problem if I put my new workbook in the D:\BB\SalesReports folder.
Debug highlights the line:
Workbooks.Open (directory & fileName)
The code:
Sub UnitSalesPrep() 'Copy raw sheets into Prep Workbook; 'Raw Workbooks must be in folder D:\BB Sales Reports; 'Folder must contain UnitSalesPrep.xlsm, run macro there. 'Code from Excel-Easy; 'https://www.excel-easy.com/vba/examples/import-sheets.html 'Declare 2 String variables, a Worksheet object, an Integer variable Dim directory As String Dim fileName As String Dim sheet As Worksheet Dim total As Integer Application.ScreenUpdating = False Application.DisplayAlerts = False 'Initialize variable directory. 'Dir function finds the first *.xl?? file in this directory. directory = "D:\BB\SalesReports" fileName = Dir(directory & "*.xl??") 'Variable fileName now holds name of 1st Excel file in directory. 'Add Do While Loop to check when Dir finds no more files. Do While fileName <> "" Loop Workbooks.Open (directory & fileName) 'Import the sheets from the Excel file into UnitSalesPrep.xlsm 'Variable total tracks total # of sheets in UnitSalesPrep.xlsm For Each sheet In Workbooks(fileName).Worksheets total = Workbooks("UnitSalesPrep.xlsm").Worksheets.Count Workbooks(fileName).Worksheets(sheet.Name).Copy after:=Workbooks("UnitSalesPrep.xlsm").Worksheets(total) Next sheet Workbooks(fileName).Close 'Get other files via Dir function, with no arguments. fileName = Dir() 'When no more files, Dir returns ("") & VBA leaves Do While loop. Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub
Lugh.
~
Alienware Aurora R6; Win10 Home x64 1803; Office 365 x32
i7-7700; GeForce GTX 1060; 16GB DDR4 2400; 1TB SSD, 256GB SSD, 4TB HD