Excel VBA .Select only works on Sheets(1)

Question

I am currently setting new designs Excel files to fit better on mobile devices. But the old designs files are also needed to some clients and I need to export the data to old design files from new design files. Old design file contains 4 Sheets. After exporting the data I want to select Cell A1 on all sheets. But I was only able to Range("A1").Select or Cells(1,1).Select on Sheets(1) only. If I set on other sheets I got "Run Time Error '1004': Select method of Range Class failed". Below are the VBA Codes along with some comments. Please help.

Set Old_CV = Application.Workbooks.Open(Old_File_Path)
ThisWorkbook.Worksheets("Data_Import").ListObjects("tbl_part2").DataBodyRange.Copy
wsTarget = ThisWorkbook.Worksheets("Data_Import").Range("rng_CV_Part2_Old")
Old_CV.Worksheets(wsTarget).Range(wsSource.Range("rng_P2_A1_Start_Old").Value).PasteSpecial xlPasteValuesAndNumberFormats

Old_CV.Activate
Old_CV.Sheets(1).Cells(1, 1).Select 'This line works even without Old_CV.Active
Old_CV.Sheets(2).Cells(1, 1).Select 'This and below lines don't work even with Old_CV.Active and showing Runtime Error
Old_CV.Sheets(3).Cells(1, 1).Select
Old_CV.Sheets(4).Cells(1, 1).Select

Please Help.

Answer 1

Activate & Select

• Select and Activate are usually to be avoided, but this task you cannot do without them.
• A good idea is to do this from the last to the first worksheet, so the first stays selected (activated).
• In the first two examples you are activating each worksheet before selecting the cell, so you need not activate the workbook.
• In the third example you have to activate the workbook first and at the end you have to select the first worksheet 'to get rid of the group'.
• The out-commented lines where used to create critical working examples in which first another workbook is active.

The Code

Option Explicit

Sub test1()
    
'    Dim Old_CV As Workbook
'    Set Old_CV = ThisWorkbook
'    Workbooks("Book2").Activate
'    ActiveSheet.Cells(1, 1).Value = 1
    
    Old_CV.Worksheets(4).Activate
    Old_CV.Worksheets(4).Cells(1, 1).Select
    Old_CV.Worksheets(3).Activate
    Old_CV.Worksheets(3).Cells(1, 1).Select
    Old_CV.Worksheets(2).Activate
    Old_CV.Worksheets(2).Cells(1, 1).Select
    Old_CV.Worksheets(1).Activate
    Old_CV.Worksheets(1).Cells(1, 1).Select

End Sub

Sub test2()
    
'    Dim Old_CV As Workbook
'    Set Old_CV = ThisWorkbook
'    Workbooks("Book2").Activate
'    ActiveSheet.Cells(1, 1).Value = 2
    
    Dim n As Long
    For n = 4 To 1 Step -1
        Old_CV.Worksheets(n).Activate
        Old_CV.Worksheets(n).Cells(1, 2).Select
    Next n

End Sub

Sub test3()
    
'    Dim Old_CV As Workbook
'    Set Old_CV = ThisWorkbook
'    Workbooks("Book2").Activate
'    ActiveSheet.Cells(1, 1).Value = 3
    
    Old_CV.Activate
    Old_CV.Worksheets(Array(1, 2, 3, 4)).Select
    ActiveSheet.Cells(1, 3).Select
    Old_CV.Worksheets(1).Select

End Sub