0

I am in the process of putting together a tool that collects data from several excel files and pastes values into a summary sheet (the same file as contains vba).

For some reason I keep getting a "runtime error 13: mismatch" error for the last line of the code so far:

 ThisWorkbook.Worksheets(ShSummary).Range("B5")

I'm guessing that maybe it doesn't like the "ThisWorkbook" reference, but I haven't had this issue before. Any ideas?

Sub Import_Data()

    Dim FilePth As String
    Dim SourceBook As Workbook
    Dim LastCell_Nbr As Integer

'Open Follow Up file
    FilePth = "C:\Users\xxx\Desktop\xxx\xxx\Follow-up_File.xlsm"
    Set SourceBook = Application.Workbooks.Open(FilePth)
    ActiveSheet.ShowAllData
    LastCell_Nbr = Workbooks("Follow-up_File.xlsm").Sheets("Follow up").Cells(Rows.Count, "C").End(xlUp).Row

'Copy and paste data values into summary sheet
    SourceBook.Sheets("Follow up").Range("A5:A" & LastCell_Nbr).Copy _
        ThisWorkbook.Worksheets(ShSummary).Range("B5")

Thanks in advance!

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Hannah
  • 1
  • 2
  • Where is `ShSummary` defined? – Comintern Feb 27 '19 at 14:02
  • ShSummary is the code name of the sheet (I.e. I renamed “Sheet 2” in case another user renames the tab at any point) – Hannah Feb 27 '19 at 14:11
  • 1
    You might want to take a look [here](https://stackoverflow.com/questions/41477794/refer-to-sheet-using-codename) – Vincent G Feb 27 '19 at 14:20
  • If `ShSummary` is the code name, you don't need to look it up in the `Worksheets` collection. `ShSummary.Range("B5")` instead of `ThisWorkbook.Worksheets(ShSummary).Range("B5")`. – Comintern Feb 27 '19 at 15:10

0 Answers0