I can't figure out why this is giving me a runtime error 9 (yes, the files are present and loaded)

Question

Sub CopyColumnWidths(FileName1, SheetName1, FileName2, SheetName2)
    ColumnNumber = 1
    Check = WorksheetFunction.CountA(Workbooks(FileName1).Sheets(SheetName1).Columns(ColumnNumber))
    Do While Check > 0
        ColumnLetter = LastColumnLetter(ColumnNumber)
        Workbooks(FileName2).Sheets(SheetName2).Columns(ColumnNumber).ColumnWidth = Workbooks(FileName1).Sheets(SheetName1).Columns(ColumnNumber).ColumnWidth
        ColumnNumber = ColumnNumber + 1
        Check = WorksheetFunction.CountA(Workbooks(FileName1).Sheets(SheetName1).Range(ColumnLetter & ":" & ColumnLetter))
    Loop
End Sub

Ok, so this is my code. I have verified that all the file names and sheet names are present and accounted for in the same instance of Excel. I have checked for misspellings, extra characters, and 'invisible' characters and none are present.

I tried, for troubleshooting purposes, putting in a Workbooks(FileName1).Activate and it wouldn't work either. In different code that particular file does get hidden, but at the time this code is executed that workbook is visible and present.

For the life of me I cannot figure out why this is breaking and could use a hand.

This is running on Excel 2013, 64-bit if it matters.

------- More Info

FileName1 is "Original Datasheet.xlsx" FileName2 is "Split Datasheet.xlsx" SheetName1 (and SheetName2) are "1a. Contents"

When I try to activate FileName2, it works. When I try to activate FileName1, it fails. The sheet names don't matter, it doesn't 'see' FileName1, even though it is present and I can select it in the 'Switch Windows' dropdown.

Repeating again for those who didn't read the title the first time: Yes, all files are loaded in the same instance of Excel. All Files Are Present.

Answer 1

I have been looking at this too long.

FileName1 was coming across as "Orignial Datasheet.xlsx" not "Original Datasheet.xlsx"

That is why it wasn't connecting. My mistake, sorry for bothering everyone.

Answer 2

I don't have your answer, but runtime error 9 sounds a lot like one of these values isn't what you think it is - and with the code you have it's hard to tell exactly where it's blowing up.

Start with turning this:

Sub CopyColumnWidths(FileName1, SheetName1, FileName2, SheetName2)

into this (assuming the procedure is called from within the same module - if it's called from another module, make it Public Sub):

Private Sub CopyColumnWidths(ByVal FileName1 As String, ByVal SheetName1 As String, ByVal FileName2 As String, ByVal SheetName2 As String)

Changing your signature to use String parameters passed by value shouldn't break your code in any way, but makes things more explicit and therefore improves readability and makes your intent clearer.

Moving on.

ColumnNumber = 1

Where's that coming from? Declare it. Stick Option Explicit at the top of your module, and then declare every variable until your code compiles again (Option Explicit will make VBA refuse to compile code that uses undeclared variables).

Dim ColumnNumber As Long
ColumnNumber = 1

Now that we know ColumnNumber and Check are local variables declared in the same scope (right?), we move on:

Dim Check As Long
Check = WorksheetFunction.CountA(Workbooks(FileName1).Sheets(SheetName1).Columns(ColumnNumber))

This line is doing too many things: we don't know that Workbooks(FileName1) is succeeding, and we don't know that its Sheets(SheetName1) is succeeding either - yet we call its Columns member regardless, assuming blue skies and sunshine.

Don't assume blue skies and sunshine.

Break it down.

Dim sourceBook As Workbook
Set sourceBook = Workbooks(FileName1)

Dim sourceSheet As Worksheet
Set sourceSheet = sourceBook.Worksheets(SheetName1)

Check = WorksheetFunction.CountA(sourceSheet.Columns(ColumnNumber))

If your code runs up to that point, your problem is half-solved - you have the same issue here, and you're fetching the same Workbook and Worksheet objects again - instead, break it down, assign local object variables, and reuse them:

Workbooks(FileName2).Sheets(SheetName2).Columns(ColumnNumber).ColumnWidth = Workbooks(FileName1).Sheets(SheetName1).Columns(ColumnNumber).ColumnWidth

Dim destinationBook As Workbook
Set destinationBook = Workbooks(FileName2)

Dim destinationSheet As Worksheet
Set destinationSheet = destinationBook(SheetName2)

destinationSheet.Columns(ColumnNumber).ColumnWidth = sourceSheet.Columns(ColumnNumber).ColumnWidth

ColumnNumber = ColumnNumber + 1
Check = WorksheetFunction.CountA(sourceSheet.Range(ColumnLetter & ":" & ColumnLetter))

Step through (F8) this code line by line, then you'll know exactly which instruction is blowing up. Cramming everything into as few code lines as possible and chaining 2, 3, 4 member accesses assuming it will "just work" makes it nearly impossible to know that.

---

Is it possible that previous code that hides the window for FIleName1 but later unhides it is messing things up? I had to do that because Excel kept putting data on the wrong workbook unless I hid it.

This sounds like you have code (elsewhere?) that works off ActiveSheet, or ActiveWorkbook, explicitly or (more likely?) implicitly.

If you find yourself using unqualified Range, Cells, Rows, Columns or Names calls, you're implicitly referring to the active sheet. Replace them with explicitly qualified member calls using the worksheet object you mean to refer to. There's no magic, Excel (VBA actually) can't guess your intentions; it doesn't "keep putting data on the wrong workbook" - it puts data exactly where you tell it to put it. Hiding the workbook is just a terribly bad work-around: you're still not telling it explicitly where you mean to put it.