Call a worksheet in a different workbook than activesheet: error-CodePudding

I need to call a worksheet("Base") in a different workbook("draft1"), while working on my active sheet. Both the active workbook("draft2") and the workbook that need to be called have a worksheet "Base" but I specifically need the worksheet in draft1. The following works, however uses the "Base" sheet in the active sheet (since workbook hasn't been specified) and returns no error.

Set C = Worksheets("Base").Range("A1").Offset(0, i)

However, when I use the following, I get a compilation error and the macro is stopped midway. Both files are saved in the same folder so this shouldn't be a problem. However, even when I call the workbook with its full address, the same error is returned. Do I perhaps need to shift the macro to the personal project thing so it can use other workbooks?

Set C = Workbooks("draft1.xlsx").Worksheets("Base").Range("A1").Offset(0, i)

Help would be appreciated!

EDIT
Here's the code snippet for the above-

Sub CreateNewMonth()
Dim i As Integer, C As Range
Application.ScreenUpdating = False
i = 1
Do While i <> 0
    Set C = Worksheets("Base").Range("A1").Offset(0, i)
    'Workbooks("draft1.xlsx").
    If IsEmpty(C) = False Then
        .
        .
        .
    End If
Loop
End Sub

There isn't any error with the loop since it is working just fine with the active sheet's "Base".

CodePudding user response：

To make this work:

Set C = Workbooks("draft1.xlsx").Worksheets("Base").Range("A1").Offset(0, i)

the file draft1.xlsx needs to be open.

Or you need to open it using the Workbooks.Open method:

Dim wbDraft As Workbook
Set wbDraft = Application.Workbooks.Open(FileName:="C:\FullPathToFile\draft1.xlsx")

Dim C As Range
Set C = wbDraft.Worksheets("Base").Range("A1").Offset(0, i)

In the end you can close the workbook using:

wbDraft.Close SaveChanges:=False

Note that the workbook the code is written in can be addressed with ThisWorkbook easily.