VBA to Activate Worksheet

[Small Paul]

Hi

There is bound to be a simple solution to this as I am probably doing something very basic wrong!

I have a workbook with 1 worksheet from a Salesforce report download (so random name - "report***************"). I am making some changes on the sheet and then copying it to a new sheet titled "Required Data". From here I will be carrying out various manipulations.

The first part of my script is:

    Columns("A:A").Select    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Worksheet Identifier: IFA Group, Account Name, Currency"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("A3").Select
    Dim lastrow As Long
lastrow = Cells(Rows.Count, "N").End(xlUp).Row
Range("A3:A" & lastrow).FormulaR1C1 = "=IF(AND(RC[30]<>""None"",RC[30]=R[-1]C[30],RC[32]=R[-1]C[32]),R[-1]C,IF(AND(RC[30]=""None"",RC[31]=R[-1]C[31],RC[32]=R[-1]C[32]),R[-1]C,R[-1]C+1))"


    Range("B2").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("B3").Select
    Range("B3:B" & lastrow).FormulaR1C1 = "=IF(C[-1]<>R[-1]C[-1],1,IF(C[10]=R[-1]C[10],R[-1],R[-1]+1))"
    


    Range("B:B").Copy
    Range("B:B").PasteSpecial Paste:=xlPasteValues
    
    
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Worksheet Identifier: Investment Product"


    Range("A4").Select
    Range("A3:A500").Copy
    Range("A3:A500").PasteSpecial xlPasteValues
    Columns("A:A").Select
    Selection.TextToColumns
    CopySheetAndRename = "Required Data"
    
    
    'THIS IS WHERE IT GOES WRONG!!!!
    Worksheets(2).Activate
    
    
    Columns("J:J").Select
    Selection.Insert Shift:=xlToRight
    Range("J:J").Value = Range("M:M").Value
    
    Columns(13).EntireColumn.delete

The PROBLEM is the final part - the script falls over at the "Worksheets(2).Activate" stage. I have tried doing it by naming the sheet in this statement, using "select" rather than "activate", using "with" etc.

As I say, it is probably something basic so if anybody can help it would be appreciated.

Many thanks
Small Paul.

 

[steve the fish]

What is the error message?

 

[Small Paul]

It simply stops the script and asks me to debug. The entire script runs fine without this small section

 

[steve the fish]

So what is the error message?

 

[steve the fish]

When you get that debug pop up it will also have a message eg Runtime error 9 Subscript out of Range.

 

[Small Paul]

Sorry Steve - it is "Run Time Error 9: subscript out of range"

 

[steve the fish]

That simply means the workbook doesnt have two worksheets.

 

[Small Paul]

But surely the 2nd sheet is created by the previous statement?