deactivate the current sheet before going to another sheet

Question

I am getting error while swapping with two sheets. I open one sheet and paste some data in this,after that I call a function which opens another sheet.but when I again paste data in the first sheet it throws error.

For y = 1 To size
    Set src = Workbooks.Open("C:Template\Mapping.xlsx")
    Worksheets("Sheet1").Activate
    Worksheets("Sheet1").Range("B" & rowCntr).Value = "abc"
    newValue= getNewValue()
    rowCntr=rowCntr+1
Next

public Function getNewValue()
    Dim newV=123
    Set src = Workbooks.Open("C:Template\Mapping - Development v1.0.xlsx")
    getNewValue=newV
End Function

For the first time it works properly but after calling the function getNewValue() it throws error "Subscript out of range" at Worksheets("Sheet1").Range("B" & rowCntr).Value = "abc" line.

Please help.

Dim newV=123 is invalid. You cannot initialize variables in VBA. — Paul Ogilvie
The issue could be something to do with the fact that you've named both your open workbooks "src". Name the second one something different. That and what Paul Ogilvie above said. you have to declare your variables and then set the value. — DDuffy

Paul Ogilvie Paul Ogilvie · Accepted Answer · 2016-05-26T11:24:53

Your code example is incomplete. Noteably, it doesn't define what src is, but that is exactly what the error is: I must assume that srcis a global variable and you set src in both your main function and in function getNewValue.

When getNewValue returns, src now points to another workbook in which your ranges don't exist.

Further I am not sure whether opening the workbook again and again results in it being reloaded, reset or that multiple copies will be opened. I suggest you open them only once, e.g.:

Dim src as Object
Dim src2 As Object

Function main (size As Integer)
    Dim y As Integer
    Dim rowCntr As Integer
    Set src = Workbooks.Open("C:Template\Mapping.xlsx")
    Set src2 =  Workbooks.Open("C:Template\Mapping - Development v1.0.xlsx")

    For y = 1 To size
        src.Worksheets("Sheet1").Activate
        src.Worksheets("Sheet1").Range("B" & rowCntr).Value = "abc"
        newValue= getNewValue()
        rowCntr=rowCntr+1
    Next
End Function

Public Function getNewValue()
    Dim newV
    newV = 123
    '...whatever you want to do...
    getNewValue = newV
End Function

deactivate the current sheet before going to another sheet

2 Answers