0

I am trying to copy all of the information in one excel file to another via VBA. I know that there are a lot of questions like this on stack (I even based my attempt at the macro on an answer to this question), but I keep getting an error. This is my code:

Sub CreateUSGCRefStepDownFile()
    Dim ParametricsWB As Workbook, ThisWB As Workbook
    Dim vFile As Variant

    'set up source files
    Set ParametricsWB = ActiveWorkbook

    vFile = Application.GetOpenFilename("Excel-files (*.xls*), *.xls*", _
        1, "Select One File to Open", , False)

    If vFile = False Then Exit Sub

    Workbooks.Open vFile


    'set up destination (this one) workbook
    Set ThisWB = ThisWorkbook


    'Create new sheets and copy and paste values into it

    'check if the sheet we want to create already exists
    Dim wsCheck As Worksheet
    Dim wsCheckExists As Boolean

    Set wsCheck = ThisWB.Sheets("Parametrics")
    On Error GoTo 0
    wsCheckExists = Not wsCheck Is Nothing


    If wsCheckExists = False Then
        Dim ws As Worksheet
        With ThisWB
            Set ws = .Sheets.Add(after:=.Sheets(.Sheets.Count))
            ws.Name = "Parametrics"
        End With
    End If

    ThisWB.Worksheets("Parametrics").Range("A1:EBT40").Value = ParametricsWB.Worksheets("ParametricAnalysis").Range("A1:EBT40").Value


End Sub

The code runs smoothly until the last line before the end of the sub. I keep getting the error: "Run-time error'-2147221080 (800401a8)': automation error". I have tried looking up the explanation of this error, but I am having trouble understanding it. I think it may have something to do with
Set ThisWB = ThisWorkbook... I have tried changing this to
Set ThisWB = ActiveWorkbook but this leads to error "Subscript out of range" when I get to Set wsCheck = ThisWB.Sheets("Parametrics"). Not really sure where I am going wrong or why it is going wrong.

Runeaway3
  • 1,439
  • 1
  • 17
  • 43
  • `ThisWorkbook` refers to the workbook with the code you're running. `ActiveWorkbook` is the active workbook. Since you do have a `wsCheck` worksheet variable, why not try that: `wsChec.Range("A1:EBT40").Value = ParametricsWB.Worksheets("ParametricAnalysis").Range("A1:EBT40").Value`? – BruceWayne Aug 08 '18 at 16:58
  • @BruceWayne, thanks for taking some time away from saving Gotham... I tried this and still get the same error. I think the issue is with the `= ParametricsWB.Worksheets("ParametricAnalysis").Range("A1:EBT40").Value`, because if I just set `wsCheck.Range("A1:EBT40").Value=4`, or some other arbitrary value, there is no error. – Runeaway3 Aug 08 '18 at 17:56
  • Try just `Debug.print ParametricsWB.Worksheets("ParametricAnalysis").Range("A1").Value`, does the console show a value? Or does that also throw an error. Triple check that the worksheet name is accurate and there's no spaces, and you've properly set the `ParametricsWB` variable. – BruceWayne Aug 08 '18 at 18:00

0 Answers0