worksheet copy

rjplante

Active Member
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?


Code:
    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

Well-known Member
Try...

Code:
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

Active Member
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:
Code:
ActiveSheet.Range("AM4").Value = Sheets(ActiveSheet.Index - 1).Range("AM4").Value + 1

Full code block:
Code:
'   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_

Board Regular
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.


Code:
    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

Active Member
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
 

Some videos you may like

This Week's Hot Topics

Top