Loop through list of filenames and corresponding sheet names

[Diffus]

This ought to be pretty easy.

I'm looking to speed up the execution of this code:

Sheets("Storage").Select
Range("StartHere").Select

Do Until ActiveCell.Value = ""
fnm = ActiveCell.Value
snm = ActiveCell.Offset(0, 1).Value
Workbooks(Currfile).Worksheets(snm).UsedRange.Clear
Workbooks.Open Filename:=Range("Input_folder").Value & "\" & fnm & ".xlsx"

With Range("A1").CurrentRegion
.WrapText = False
.ShrinkToFit = False
.UnMerge
End With
Workbooks(fnm).Worksheets("Sheet").UsedRange.Copy Workbooks(Currfile).Worksheets(snm).Range("A1")
Workbooks(fnm).Close SaveChanges:=False
Sheets("Storage").Select
ActiveCell.Offset(1, 0).Select
Loop

It loops through a list in a workbook tab, with a filename in column A and a corresponding sheetname in column B, copying the contents of the file to the corresponding sheet. I know there ought to be a way to read the names into an array and pull them from there, rather than selecting them from worksheet cells, but I don't have any experience in doing that, and I am looking to learn.

Thanks in advance.

 

[6StringJazzer]

The most costly thing you do by far is open a file, copy and paste, and close the file. Compared to that nothing else you do will matter very much.

However, you should not be selecting a cell just to reference it. This is more standard, and may give you a slightly faster sub. However, there is no way to know from this code what is the activecell when the code executes (which is one reason to avoid references to ActiveCell). I am going to guess it is the first filename in column A. I also recommend explicit object qualifications.

Rather than Workbooks(CurrFile), I suggest ThisWorkbook if the code is in the same workbook receiving the data.

Sub CopyMultiple()

   Dim FileNameC As Range
   Dim SheetNameC As Range
   Dim WB As Workbook
   Dim IntSheet As Worksheet, ExtSheet As Worksheet
   
   Set FileNameC = ThisWorkbook.Worksheets("File Names").Range("A1")
   Set SheetNameC = FileNameC.Offset(0, 1)
   
   Do Until FileNameC.Value = ""
   
      Set IntSheet = ThisWorkbook.Worksheets(FileNameC)
      Set WB = Workbooks.Open(Filename:=Range("Input_folder").Value & "\" & FileNameC & ".xlsx")
      Set ExtSheet = WB.Worksheets("Sheet")
      
      IntSheet.UsedRange.Clear
      
      With ExtSheet.Range("A1").CurrentRegion
         .WrapText = False
         .ShrinkToFit = False
         .UnMerge
      End With
      
      ExtSheet.UsedRange.Copy IntSheet.Range("A1")
      
      WB.Close SaveChanges:=False
      
      Set FileNameC = FileNameC.Offset(1, 0)
      Set SheetNameC = SheetNameC.Offset(1, 0)
      
   Loop

End Sub

 

[johnnyL]

@Diffus Several questions need to be answered to help you speed up your code.

1) Is the code you posted currently working?
2) It appears that the 'fnm' & 'snm' variables that you mention refer to columns A & B in the sheet named 'Storage' in this workbook that contains this code? What are the Address ranges for those?
3) Which workbook is 'Currfile' referring to? A different workbook?
4) 'Input_folder'? Where is that referenced to? Which workbook/sheet/address?

The code could definitely be sped up but the addresses/sheets/workbooks involved need to be clarified.

 

[Diffus]

Over the years,, I've developed code that I just keep going back to once I've figured out how to make something work. I've always been too busy working stupid to invest the time necessary to figure out how to work smarter. I'm taking a little of that time now, spending some time learning how to clean up macro recorder code more than I have and avoid selecting worksheets and cells when I can.

To answer the questions:
1) Yes, the code works.
2) The filenames and sheet names are in A28:B45 A28 is named StartHere), although that will change as I import more files or decide that some aren't needed.
3) Currfile is the file in which the VBA code resides, the file into which the data from multiple files are being imported.
4) Input_folder is a cell in a worksheet wherein I input the name of the folder in which the files being imported reside. I used to have two options, but, lately, it's been D:\Input.

The purpose of this routine is to open the .xlsx files in A28 through A45 and copy their contents onto the tabs referenced in B28 through B45. Once the data are imported into these tabes, formulae in other tabs generate the reports that management wants to see.

 

[johnnyL]

Input_folder ... I asked for the location of that cell.

 

[Diffus]

Input_folder ... I asked for the location of that cell.

Sorry. B23.

 

[johnnyL]

B23 of which workbook and which work sheet?

 

[Diffus]

B23 of which workbook and which work sheet?

The sheet name is "Storage," and it's in the file into which the data are being imported. "Currfile" in my syntax, ThiwWorkbook, according to a revision suggested previously. The file names to import and the names of the tabs onto which they are being imported are in A28:B43 in "Storage," and the input folder is stored in B23 of Storage.

 

[johnnyL]

Ok, I think you have supplied enough info now. I have to catch some sleep now, I should be back online in about 12 hours, when I shall tackle your question then, if noone else has done so in the mean time.

 

[6StringJazzer]

if noone else has done so in the mean time.

Did you see post #2?