worksheet copy

[rjplante]

I have a list of all the worksheets in my workbook on tab 1. I want the user to put a number next to the sheet in column B and have the macro copy the selected worksheet one less times than the number listed. If in the end I want 3 copies of the worksheet, I want to make two copies of the selected sheet. When I run my macro it fails on this line (wsN = ActiveSheet) with a run time error '91': object variable or with block variable not set. How do I fix this?

    Dim CopyCnt As Integer
    Dim wsCnt As Integer
    Dim wsN As Worksheet

'   Find worksheet that matches text


    For Each Sheet In ActiveWorkbook.Sheets
    
        If Sheet.Name Like "*" & ActiveCell.Offset(0, 13).Value & "*" Then
            Sheet.Activate
            wsN = ActiveSheet
            Exit For
        End If
    Next
    
'   Duplicate selected worksheet
    
    n = 1
    
    Do Until n = CopyCnt
        wsN.Copy After:=wsN
        n = n + 1
    Loop

Thanks for the help!

 

[Snakehips]

Try...

For Each Sheet In ActiveWorkbook.Sheets    
        If Sheet.Name Like "*" & ActiveCell.Offset(0, 13).Value & "*" Then
            Sheet.Activate
[COLOR=#ff0000]           Set wsN = ActiveSheet[/COLOR]
            Exit For
        End If
    Next

Hope that helps.

 

[rjplante]

Thanks Tony, that little "Set" addition worked great. Now for another problem, when I run the macro the second DO loop I have runs through all the worksheets updating the cell AM4 as desired. When it reaches the last worksheet, it updates AM4 and then flags a Run Time error '1004' stating that the cell or chart I am trying to change is on a protected sheet. I have checked and I do not have any protected sheets in the workbook. When I select debug from the pop up box, the following line is highlighted.

Error line:

ActiveSheet.Range("AM4").Value = Sheets(ActiveSheet.Index - 1).Range("AM4").Value + 1

Full code block:

'   Update test number count T-00
    
    Sheets("T-01 VAL-4840 Control Box ").Activate
    Range("AM4").Value = 1
    Sheets(ActiveSheet.Index + 1).Activate
    
    Do
        ActiveSheet.Range("AM4").Value = Sheets(ActiveSheet.Index - 1).Range("AM4").Value + 1
        ActiveSheet.Name = [CA1]
        If ActiveSheet.Index < Worksheets.Count Then
            Sheets(ActiveSheet.Index + 1).Activate
        Else
            Exit Do
        End If
    Loop

Since I do not have any protected sheets, and the cell does get updated prior to failure, what is causing the problem?

 

[Steve_]

I feel like your results would be much easier to control and errors would be limited if you were using For..Each to iterate through your worksheets.

    For Each xlSheet In ThisWorkbook.Sheets
        debug.print xlSheet.Name   'This is just to show you how to refer to the sheets properties/objects
        'Your code here.
    Next xlSheet

 

[rjplante]

My desire to use a do loop function was that I could start at the middle of the work book and work to the end. Can I do the same thing with a for...each approach and iterate through the remaining worksheets? If so what does the first half of the set up code look like?

Thanks