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]

So it's kind of a catch 22. I can bypass any errors by closing the workbook at the end of each loop iteration -- but it is a large workbook and seemingly inefficient to open/close several hundred times.

If don't close the workbook, I'm getting a Runtime Error 9 - Subscript out of range on the lines below, only after the 2nd pass of the loop (first pass runs without error):

   START_DATE = MasterWB.Sheets("MASTER_LIST").Cells(2, 8).Value
      END_DATE = MasterWB.Sheets("MASTER_LIST").Cells(3, 8).Value

 

[Andrew Poulsom]

If MasterWB is the workbook containing your code you can use:

START_DATE = Thisworkbook.Sheets("MASTER_LIST").Cells(2, 8).Value

What's in that cell when you get the error?

 

[bs0d]

Ok, I think that worked and passed the buck to another error. There are dates in the cells referenced, which are static.

Now I'm seeing: "Run-time Error 91: Object variable or With block variable not set". Its pointing to this function which I plucked from the forums:

Public Function WorksheetExists(WB, ByVal WorksheetName As String) As Boolean
'http://www.mrexcel.com/forum/excel-questions/3228
 Dim Sht As Worksheet
    'For Each Sht In ThisWorkbook.Worksheets
     For Each Sht In WB.Worksheets
        If Application.Proper(Sht.Name) = Application.Proper(WorksheetName) Then
             WorksheetExists = True
             Exit Function
        End If
    Next Sht
 
 WorksheetExists = False
 
 End Function

Again, what's interesting is that this works if WB is closed after each iteration.

Here is how I call the function:

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

 

[Andrew Poulsom]

What's assigned to WB when you call that function? It seems nothing.

 

[bs0d]

Yeah, I guess nothing is assigned because its already open?

This is the code where WB is set, which is right above the code posted earlier (chkSh =WorksheetExists(WB, Name)

'Confirm workbook:
If BookOpen(WB_NAME & ".xlsx") = False Then

Application.DisplayAlerts = False 'Suppress any alerts @ WB open
    Dim WB As Excel.Workbook
        Set WB = Workbooks.Open("L:\Production - Historical\" & WB_NAME & ".xlsx")
Application.DisplayAlerts = True 'Turn back on

    If WB Is Nothing Then
        Z = MsgBox("Alert: Workbook - " & WB_NAME & " - Not Found.", vbOKOnly, "ERROR")
        GET_PROD = False
        Exit Function
    End If

End If

 

[Andrew Poulsom]

Don't you assign it to WB when you open it? More likely it isn't open.

 

[bs0d]

Yes, WB is assigned inside this condition that checks if it's open or not.

'Confirm workbook:
If BookOpen(WB_NAME & ".xlsx") = False Then

Application.DisplayAlerts = False 'Suppress any alerts @ WB open
    Dim WB As Excel.Workbook
        Set WB = Workbooks.Open("L:\Production - Historical\" & WB_NAME & ".xlsx")
Application.DisplayAlerts = True 'Turn back on

    If WB Is Nothing Then
        Z = MsgBox("Alert: Workbook - " & WB_NAME & " - Not Found.", vbOKOnly, "ERROR")
        GET_PROD = False
        Exit Function
    End If

End If

I think I need to add ELSE and assign it to WB. Here is a stab, but the syntax doesn't work:

Else
   Set WB = WB_NAME & ".xlsx"

Object required error

 

[Andrew Poulsom]

At what stage are you calling the function WorksheetExists?

 

[bs0d]

At what stage are you calling the function WorksheetExists?

Here is my full code for the function that is called. Keep in mind, I'm no expert:

Function GET_PROD(NAME, ID, TAB_NAME, WB_NAME, START_CELL, START_COL, END_COL)

'define current workbook:
Dim MasterWB As Excel.Workbook
Set MasterWB = ActiveWorkbook

'Get Dates:

    START_DATE = ThisWorkbook.Sheets("MASTER_LIST").Cells(2, 8).Value
      END_DATE = ThisWorkbook.Sheets("MASTER_LIST").Cells(3, 8).Value
    
Dim WB As Excel.Workbook
    
'Confirm workbook:
If BookOpen(WB_NAME & ".xlsx") = False Then

Application.DisplayAlerts = False 'Suppress any alerts @ WB open
    
        Set WB = Workbooks.Open("L:\Production - Historical\" & WB_NAME & ".xlsx")
Application.DisplayAlerts = True 'Turn back on

    If WB Is Nothing Then
        Z = MsgBox("Alert: Workbook - " & WB_NAME & " - Not Found.", vbOKOnly, "ERROR")
        GET_PROD = False
        Exit Function
    End If

Else

    'Dim WB As Excel.Workbook
    Set WB = WB_NAME & ".xlsx"

End If

    'Verify Tab Name exists:
    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"
         
         WB.Close False 'close opened workbook.
         
         GET_PROD = False
         
        Exit Function
    End If
    
        'Loop to find start and end dates:
        WLastRow = WB.Sheets(NAME).Cells(Rows.Count, 1).End(xlUp).Row
        
        For c = START_CELL To WLastRow
            If WB.Sheets(NAME).Cells(c, 1).Value = START_DATE Then
                copyFrom = WB.Sheets(NAME).Cells(c, 1).Row
            ElseIf WB.Sheets(NAME).Cells(c, 1).Value = END_DATE Then
                copyTo = WB.Sheets(NAME).Cells(c, 1).Row
            End If
        Next c
        
        'Validate dates, or error-out (skip):
        If (copyFrom <= 0) Or (copyTo <= 0) Then

                '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 = "One or both dates missing. From date: " & copyFrom & " - to date: " & copyTo & "."
              
              WB.Close False 'close opened workbook
              GET_PROD = False
            Exit Function
        End If
        
        'Detect last row
         FinalRow = MasterWB.Sheets(TAB_NAME).Cells(Rows.Count, 1).End(xlUp).Row
            If FinalRow <= 0 Then
                FinalRow = 1
            End If
        
        'Copy & paste production history:
        WB.Sheets(NAME).Range(WB.Sheets(NAME).Cells(copyFrom, START_COL), WB.Sheets(NAME).Cells(copyTo, END_COL)).Copy MasterWB.Sheets(TAB_NAME).Cells(FinalRow + 1, 3)

            NewFinalRow = (FinalRow + 1) + (copyTo - copyFrom)
        
        'Populate Name & ID next to each row of production recently pasted
        For zzTop = FinalRow + 1 To NewFinalRow
            MasterWB.Sheets(TAB_NAME).Cells(zzTop, 1).Value = ID
            MasterWB.Sheets(TAB_NAME).Cells(zzTop, 2).Value = NAME
        Next zzTop


'close opened workbook:
'WB.Close False
        
GET_PROD = True 'Everything successful

End Function

So I check if WorksheetExists after I check if BookOpen

 

[Andrew Poulsom]

This:

Set WB = WB_NAME & ".xlsx"

should be:

Set WB = Workbooks(WB_NAME & ".xlsx")