VBA 1004 error on second time through loop

[Mr Smin]

This macro is intended to get a range from one sheet and put it on a freshly created sheet, then get the next range and copy to another new sheet. It works correctly first time round, but on the second time at Select it gives a 1004 application- or object-definition error.
The debug.print shows that the row numbers are as expected in the second run.
Can anyone suggest a fix?

Sub ProcessStudies()
Dim firstrow As Long
Dim lastrow As Long
'requires UniqueItems function be placed in Modules
Set wbToAddSheetsTo = ActiveWorkbook
Dim rng As Range
Debug.Print "set rng"
Set rng = Sheet2.Range("H2:H1000")


'Set Studies = Range("H2:H" & lastRow & "")
Set Studies = Sheet2.Range("H2:H1000")
    arrStudies = UniqueItems(Studies, False) 
    Dim i As Integer
    For i = LBound(arrStudies) To UBound(arrStudies)
        Debug.Print "starting " & arrStudies(i)
        With wbToAddSheetsTo
        
   
    firstrow = findValues("first", "" & arrStudies(i) & "", rng)
    Debug.Print "firstrow " & firstrow
    lastrow = findValues("last", "" & arrStudies(i) & "", rng)
    Debug.Print "lastrow " & lastrow
            Sheet2.Range("A" & firstrow & ":AH" & lastrow & "").Select
            Debug.Print "copy"; ActiveSheet.Name
            Selection.Copy
            Debug.Print "add new sheet"
            .Sheets.Add After:=.Sheets(.Sheets.Count)
            Debug.Print "name as " & arrStudies(i)
            ActiveSheet.Name = arrStudies(i)
            Debug.Print "paste " & arrStudies(i)
            ActiveSheet.Paste
            Debug.Print "named new sheet as " & arrStudies(i)
        End With
    Next i
    
    End Sub

 

[michaeljryan78]

Looks like you are still manipulating sheet2 in the code. even on the second loop.

"Sheet2.Range("A" & firstrow & ":AH" & lastrow & "").Select"
are you wanting to activate the neext sheet in wbToAddSheetsTo ?

 

[Mr Smin]

I need to get rows from Sheet2 and paste them to a series of new sheets.
In the first run I might copy rows 2 through 13 from Sheet2 and paste to Sheet3, in the second run copy rows 14 to 29 from Sheet2 and paste to Sheet4 and so on.
Does that make sense?

 

[Mr Smin]

Looks like I wasn't being clear to Excel which object I was talking about. I changed it as follows

For Each openStudy In openStudies.keys()
      
    firstrow = findValues("first", "" & openStudy & "", rng)
    lastrow = findValues("last", "" & openStudy & "", rng)

    Worksheets("MASTER").Activate
    ActiveSheet.Range("A" & firstrow & ":AJ" & lastrow & "").Select

    Selection.Copy

    Worksheets("" & openStudy & "").Activate
    ActiveSheet.Range("A2").Select
    ActiveSheet.Paste
    
Next