VBA - Copying A Worksheet Into Open File From A Closed File

[SeaTigr]

Good afternoon, all! I appreciate any and all assistance!

I'm modifying an Excel file I inherited. It allows users to select input files and then performs calculations with that input. The problem is the number of calculations. I had to expand it from 1,440 rows to 17,280 rows. One column calls an input file 15 times per cell. Combined with other cells, it's now taking 20-30 seconds to recalculate values every time I change something. I turned off automatic recalculating, but I'd like to modify the VBA script to simply copy specific sheets from the selected input files (i.e., the sheets with values, not the revision history and title sheets) and put them in sheets within my main workbook. I'll then update the formulae to reference the sheets within the workbook rather than calling the external files. I'm trying to insert the sheets at the very back of my main file, but it doesn't seem to do that.

I am a terrible coder, so I've been working on modifying the existing VBA script using help from the Oracle of Google. But I keep running into Run-time error '9': subscript out of range errors with the line intended to close the workbooks. I thought I was using the correct syntax. So I'm not even getting to the other two files - it quits on the bus part.

The FromRow is referencing a worksheet in the main file where users can mark which input files they want with an 'x', then execute the RENAME_FILES script to grab the data in those files.


Sub RENAME_FILES()
Dim HomeFolder As String
Dim ToFolder As String
Dim FromSheet As Worksheet
Dim FromFileName As String
Dim ToFileName As String
Dim FromRow As Long

'=====================================================================================================
HomeFolder = ThisWorkbook.Path & "\background\Bus Modules\"
'ToFolder = ThisWorkbook.Path & "\background\"
'-
Set FromSheet = Worksheets("File Input")
FromRow = 4 'FIRST ROW CONTAINING NAMES
'- run down list until blank cell
While FromSheet.Cells(FromRow, 6).Value <> ""
If FromSheet.Cells(FromRow, 7) = "x" Then
FromFileName = HomeFolder & FromSheet.Cells(FromRow, 6).Value
ControlFile = ActiveWorkbook.Name
Workbooks.Open (FromFileName)
Sheets("Bus Values").Copy After:=Workbooks(ControlFile).Worksheets(Worksheets.Count)
Workbooks(FromFileName).Close SaveChanges:=False
End If
FromRow = FromRow + 1
Wend
'=====================================================================================================
HomeFolder = ThisWorkbook.Path & "\background\Solar Array Modules\"
ToFolder = ThisWorkbook.Path & "\background\"
'-
Set FromSheet = Worksheets("File Input")
FromRow = 4 'FIRST ROW CONTAINING NAMES
'- run down list until blank cell
While FromSheet.Cells(FromRow, 9).Value <> ""
If FromSheet.Cells(FromRow, 10) = "x" Then
FromFileName = HomeFolder & FromSheet.Cells(FromRow, 9).Value
ControlFile = ActiveWorkbook.Name
Workbooks.Open (FromFileName)
Sheets("Solar Array 1").Copy After:=Workbooks(ControlFile).Worksheets(Worksheets.Count)
Sheets("Solar Array 2").Copy After:=Workbooks(ControlFile).Worksheets(Worksheets.Count)
Workbooks(FromFileName).Close SaveChanges:=False
End If
FromRow = FromRow + 1
Wend
'=====================================================================================================

'=====================================================================================================
HomeFolder = ThisWorkbook.Path & "\background\Battery Modules\"
ToFolder = ThisWorkbook.Path & "\background\"
'-
Set FromSheet = Worksheets("File Input")
FromRow = 4 'FIRST ROW CONTAINING NAMES
'- run down list until blank cell
While FromSheet.Cells(FromRow, 15).Value <> ""
If FromSheet.Cells(FromRow, 16) = "x" Then
FromFileName = HomeFolder & FromSheet.Cells(FromRow, 6).Value
ControlFile = ActiveWorkbook.Name
Workbooks.Open (FromFileName)
Sheets("Battery").Copy After:=Workbooks(ControlFile).Worksheets(Worksheets.Count)
Sheets("I, V, SOC").Copy After:=Workbooks(ControlFile).Worksheets(Worksheets.Count)
Workbooks(FromFileName).Close SaveChanges:=False
End If
FromRow = FromRow + 1
Wend
'=====================================================================================================

End Sub