VBA - Activate opened workbook

Question

I want to read a workbook and copy some cells in another workbook. The aim of the code is to copy the cells from the different sheets from one file to the other.

For this propose, I created this code:

Sub processCopy(file)

'Abrir documento
Workbooks.Open Filename:=file, UpdateLinks:=0


cantn = ThisWorkbook.Sheets.Count
cantv = Sheets.Count - 5
cantn = cantn - 4

'Recorrer los libros del documento abierto
For i = 1 To (Sheets.Count - 5)

    'Obtener nombre del libro seleccionado
    nombre = Sheets(i + 2).Name
    'Filtrar los libros no necesarios
    If nombre <> "Instructions" And nombre <> "Executive Summary" And nombre <> "Process Update" And nombre <> "Template" And nombre <> "Notes" Then

        If cantv >= cantn Then
         ThisWorkbook.Sheets(3).copy after:=ThisWorkbook.Sheets(cantn + 2)
         cantn = cantn + 1
         ThisWorkbook.Sheets(cantn + 2).Name = nombre
        ElseIf nombre <> ThisWorkbook.Sheets(cantn + 2).Name Then
         ThisWorkbook.Sheets(cantn + 2).Name = nombre
        End If

        Workbooks.Open Filename:=file, UpdateLinks:=0

        ActiveWorkbook.Worksheets(nombre).Activate

        ActiveWorkbook.Sheets(nombre).Range("C4:O23").Select
        Selection.copy
        ThisWorkbook.Sheets(nombre).Range("C4:O23").PasteSpecial

After finishing the process in the first sheet, I compare if in the new file there is any other sheet to start with the process. If not (always) I create a new sheet to start with the process of copying and pasting, but the functions ThisWorkbook and ActiveWorkbook are working on the same sheet (copying and pasting from the same Workbook) and not from one file to the other.

That is why, I open the first workbook, but when the process of copying and pasting finishes, this message appears:

Error image

What can i do to solve it this issue?

Thanks for all

gizlmo gizlmo · Accepted Answer · 2016-09-08T13:27:35

Create a Workbook Object and assign the Workbook you are opening to it:

Dim wb as Workbook
Set wb = Workbooks.Open(Filename:=file, UpdateLinks:=0)

Then you are able to refer to this variable instead of ActiveWorkbook

So to change things in the Workbook the code resides in, use ThisWorkbook

And to change things in the other Workbook, use wb

VBA - Activate opened workbook

1 Answers