Error '1004' & '13' Copying Range from Another Open Workbook.

[bs0d]

I'm building a macro within one workbook, to open another WB and copy a range of data, then go back to the original WB and paste the results.

With the code below, I get a '1004' error (application or object defined error):

WB.Sheets(NAME).Range(Cells(copyFrom, START_COL), Cells(copyTo, END_COL)).Select
Selection.Copy

After researching '1004' errors, I tried the code below, but now get a '13' error (type mismatch):

Dim copy_rng as Range
Set copy_rng = WB.Sheets(NAME).Range("A" & copyFrom).Resize(copyTo, END_COL)

Other information you may need:

WB is defined as Excel.Workbook, the 2nd workbook I'm trying to copy from.
CopyFrom, copyTo and END_COL contain integers.
NAME is a string variable or the tab name.

Any thoughts?

 

[bs0d]

Excel is really making things tough, as correcting that issue again just jumped to another conditional "error" (existing only after the first loop, and only if a workbook is already open).

Now it doesn't like this code, which says '9' subscript out of range. This FinalRow check is the one that occurs right before copying & pasting:

FinalRow = MasterWB.Sheets(TAB_NAME).Cells(Rows.Count, 1).End(xlUp).Row

Any thought as to why VBA doesn't like this?

 

[Andrew Poulsom]

That means that a sheet with the value assigned to TAB_NAME doesn't exist in MasterWB. What's assigned to TAB_NAME when you get the error?

 

[bs0d]

Hey Andrew,

The tab name does exist, because I don't get the error if I close the workbook at the end of each loop cycle. Also, before this point in the code, I have a condition to check if TAB_NAME exists - and if it doesn't, to exit the function:

chkSh = WorksheetExists(WB, NAME)
    
    If chkSh = False Then

Z = MsgBox("Alert: Worksheet - " & NAME & " - Not Found in " & WB_NAME & ".", vbOKOnly, "ERROR")
    
        'Detect last row
         FinalRow = MasterWB.Sheets("ERRORS").Cells(Rows.Count, 1).End(xlUp).Row
         
            If FinalRow <= 0 Then
                FinalRow = 1
            End If
            
         MasterWB.Sheets("ERRORS").Cells(FinalRow + 1, 1).Value = NAME
         MasterWB.Sheets("ERRORS").Cells(FinalRow + 1, 2).Value = "Tab Names does not match Name"
         
         WB.Close False 'close opened workbook.
         
         GET_PROD = False
         
        Exit Function
    End If

 

[Andrew Poulsom]

I don't see any reference to TAB_NAME in that code. If it existed you wouldn't get an error.

 

[bs0d]

You're right, that check is to ensure the tab name in the other workbook exists.

I think I found a solution, and that is to use "ThisWorkbook." instead of "MasterWB." ... After the 1st loop, it seems to forget the "MasterWB" being set.

On the code where you showed me how to copy and paste:

WB.Sheets(NAME).Range(WB.Sheets(NAME).Cells(copyFrom, START_COL), WB.Sheets(NAME).Cells(copyTo, END_COL)).Copy ThisWorkbook.Sheets(TAB_NAME).Cells(FinalRow + 1, 3)

How can I tell Excel to pastespecial values here?

 

[Andrew Poulsom]

That would require two lines:

WB.Sheets(NAME).Range(WB.Sheets(NAME).Cells(copyFrom, START_COL), WB.Sheets(NAME).Cells(copyTo, END_COL)).Copy
 ThisWorkbook.Sheets(TAB_NAME).Cells(FinalRow + 1, 3).PasteSpecial  Paste:=xlPasteValues

 

[bs0d]

Great, that works like a charm. I appreciate the help- This macro will save a lot of time.