Conversion from Windows XP to Windows 2007 has killed my macro - Run-time Error 1004

Hi,

I had the following code which was working when I was under Windows XP. After converting to Windows 2007, I tried running the same macro to no avail. Run-time error 1004 would pop up. The debugger would highlight the Copy Before action. Any advice is greatly appreciated!

Sub RegionSplit()
    Dim nodupes As New Collection
    Dim OutWB As Workbook
    Dim strRONum As String
  
    'determine a list of unique regions
    With Sheets("Report")
        On Error Resume Next
        For Each ce In .Range(.Range("F9"), .Cells(Rows.Count, "F").End(xlUp))
            nodupes.Add Item:=ce.Value, Key:=ce.Value
        Next ce
        On Error GoTo 0
    End With
  
    lstdatarow = Sheets("Report").Cells(Rows.Count, 1).End(xlUp).Row
    lstcsltrow = Sheets("Cslt_Detail").Cells(Rows.Count, 1).End(xlUp).Row
  
    For i = 1 To nodupes.Count
        Workbooks.Add

        Set OutWB = ActiveWorkbook
        ThisWorkbook.Activate
        Sheets("Reference").Copy Before:=OutWB.Sheets(1)        
        ThisWorkbook.Activate
        Sheets("Report").Copy after:=OutWB.Sheets(1)
        ThisWorkbook.Activate
        Sheets("Cslt_Detail").Copy after:=OutWB.Sheets(2)
        ThisWorkbook.Activate
        OutWB.Sheets("Report").Rows("9:" & lstdatarow).ClearContents
        OutWB.Sheets("Cslt_Detail").Rows("3:" & lstcsltrow).ClearContents
      
        With Sheets("Report")
            .Range("H1").Value = Sheets("Report").Range("F8").Value
            .Range("H2").Value = nodupes(i)
            .Range(.Range("A8"), .Range("AO" & .Cells(Rows.Count, 1).End(xlUp).Row)).AdvancedFilter , Action:=xlFilterCopy, criteriarange:=.Range("H1:H2"), copytorange:=OutWB.Sheets("Report").Range("A8:AO8")
        End With
    
        lstdatarowreg = OutWB.Sheets("Report").Cells(Rows.Count, 1).End(xlUp).Row
        OutWB.Sheets("Report").Range("A" & lstdatarowreg + 1 & ":AO" & lstdatarowreg + 1).ClearFormats
        OutWB.Sheets("Report").Range("A" & lstdatarowreg + 1 & ":AO" & lstdatarowreg + 1).Interior.ColorIndex = 2
        OutWB.Sheets("Report").Range("A" & lstdatarowreg + 2 & ":A65000").EntireRow.Delete
        strRONum = "RO " & Format(OutWB.Sheets("Report").Range("A9"), "000")
    
        With Sheets("Cslt_Detail")
            .Range("AS1").Value = Sheets("Cslt_Detail").Range("B2").Value
            .Range("AS2").Value = nodupes(i)
            .Range(.Range("A2"), .Range("AR" & .Cells(Rows.Count, 1).End(xlUp).Row)).AdvancedFilter , Action:=xlFilterCopy, criteriarange:=.Range("AS1:AS2"), copytorange:=OutWB.Sheets("Cslt_Detail").Range("A2:AR2")
        End With
    
        lstcsltrowreg = OutWB.Sheets("Cslt_Detail").Cells(Rows.Count, 1).End(xlUp).Row
        OutWB.Sheets("Cslt_Detail").Range("A" & lstcsltrowreg + 1 & ":A65000").EntireRow.Delete
          
        OutWB.Activate
    
        Application.DisplayAlerts = False
    
        For j = Sheets.Count To 4 Step -1
            Sheets(j).Delete
        Next j
    
        Application.DisplayAlerts = True
    
        Sheets("Report").Range("H1:H2").ClearContents
        ActiveWorkbook.SaveAs Filename:="C:\Temp\" & Left(ThisWorkbook.Name, _
            Len(ThisWorkbook.Name) - 20) & strRONum, FileFormat:=51
        Sheets("cslt_Detail").Range("AS1:AS2").ClearContents
        Sheets("Reference").Activate
        Range("A1").Select
        OutWB.Close savechanges:=True
    
    Next i
    
    Sheets("Report").Range("H1:H2").ClearContents
    Sheets("cslt_Detail").Range("AS1:AS2").ClearContents

End Sub

Thank you!

Hi abousetta,

Thank you for the quick reply, I will try this tomorrow when I am back at work. The code is basically trying to copy from the main workbook to generate multiple workbooks (one per region based on a region column identifier). I am not well-versed with VB and I'm sure there's a much cleaner way to get the result (your suggestion definitely looks neater). At the time when I was in Windows XP, the original code did work, but for some reason after converting to Windows 7, it went wonky.

Much appreciated! I will follow-up tomorrow.

Good morning,

Inputting the above code gets me a new error - Run-time error 9 (Subscript out of range). Debugging highlights the following:

Sheets("Reference").Copy Before:=Sheets(1)