Error 13 when Pasting from another Workbook

Question

I'm new to VBA, and I've been trying to paste some data from one file into my active file. Unfortunately, I've been getting error 13 - Type Mismatch. I've tried changing each of the variable definitions, even declaring them as Variant, but nothing helped. The most relevant part of the code is below, with the error between the asterisks.

dim i, j, k, CompShtStartNum, CompShtQty as integer
dim OldFile as variant
dim WCompWS, WCOl, NumEntryCol, ShtName as string
dim InputsSht as worksheet
dim NumEntryColRange, OldEntryCount as range


'Paste data from Entry Label columns into comparison sheets
    'Paste in the data from the old file
        For i = CompShtStartNum To CompShtStartNum + CompShtQty - 1
            ShtName = ThisWorkbook.Sheets(i).Name
            Set OldSht = OldFile.Sheets(ShtName)
            Set OldEntryCount = Range(OldSht.Cells(2, 1), OldSht.Cells(Rows.Count, 1).End(xlDown))
            For j = 1 To CompShtStartNum - i + 1
                For k = 1 To InputsSht.Range(WCol & 12 + j - 1).Value
                     If OldFile.Sheets(i).Cells(1, k).Value = Sheets(i).Cells(1, k).Value Then
                        ***Sheets(i).Cells(2, k).Resize(OldEntryCount.Rows.Count, 1).Value = Application.Transpose(OldEntryCount.Value)***
                    End If
                Next k
            Next j
       Next i

For context, here is the full code:

Set OldFile = Application.Workbooks("Old Input File.xlsx")
    Let WCompWS = "E"
    Let WCol = "F"
    Let CompShtStartNum = 2
    Set InputsSht = ThisWorkbook.Sheets("Inputs")
    Let CompShtQty = InputsSht.Range(WCompWS & 12, InputsSht.Range(WCompWS & 12).End(xlDown)).Count

'Loop thru each sheet and have the user determine the last column of labels.  Paste result on Inputs sheet.
    For i = CompShtStartNum To CompShtStartNum + CompShtQty - 1
        ShtName = ThisWorkbook.Sheets(i).Name
        Sheets(ShtName).Activate
        NumEntryCol = Application.InputBox("How many columns (from the left-hand side) contain entry labels?" & vbNewLine & "(Examples of entry labels: Library #, Entry #, etc.)" & vbNewLine & vbNewLine & "Please type your answer numerically.", ShtName)
        InputsSht.Range(WCol & 12 + i - CompShtStartNum).Value = NumEntryCol
    Next i
    Set NumEntryColRange = InputsSht.Range(WCol & 12, InputsSht.Range(WCol & 12).End(xlDown))
    InputsSht.Activate

'Paste data from Entry Label columns into comparison sheets
    'Paste in the data from the old file
        For i = CompShtStartNum To CompShtStartNum + CompShtQty - 1
            ShtName = ThisWorkbook.Sheets(i).Name
            Set OldSht = OldFile.Sheets(ShtName)
            Set OldEntryCount = Range(OldSht.Cells(2, 1), OldSht.Cells(Rows.Count, 1).End(xlDown))
            For j = 1 To CompShtStartNum - i + 1
                For k = 1 To InputsSht.Range(WCol & 12 + j - 1).Value
                     If OldFile.Sheets(i).Cells(1, k).Value = Sheets(i).Cells(1, k).Value Then
                        ***Sheets(i).Cells(2, k).Resize(OldEntryCount.Rows.Count, 1).Value = Application.Transpose(OldEntryCount.Value)***
                    End If
                Next k
            Next j
       Next i

Any help or suggestions would be very appreciated!!

Answer 1

However, the result was only the value in A2 being pasted into A2:A7 on the active file sheet. How can I get each of the values in A2:A7 to paste into their respective cells on my active sheet?

Try this

Sheets(i).Cells(2, k).Resize(OldEntryCount.Rows.Count, 1).Value = _
OldEntryCount.Value

Here is a short demonstration. Let's say our worksheet looks like this

Now lets say we want the values of A1:A5 in B1:B5 in Sheet1

Simply try this

Sub Sample()
    Dim OldEntryCount As Range

    With ThisWorkbook.Sheets("Sheet1")
        Set OldEntryCount = .Range("A1:A5")

        .Range("B1").Resize(OldEntryCount.Rows.Count, 1).Value = _
        OldEntryCount.Value
    End With
End Sub

And you will get the result