Results 1 to 5 of 5

Thread: worksheet copy
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular rjplante's Avatar
    Join Date
    Oct 2008
    Posts
    413
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default worksheet copy

    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!

  2. #2
    Board Regular Snakehips's Avatar
    Join Date
    May 2009
    Location
    Coventry UK
    Posts
    4,671
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: worksheet copy

    Try...

    Code:
    For Each Sheet In ActiveWorkbook.Sheets    
            If Sheet.Name Like "*" & ActiveCell.Offset(0, 13).Value & "*" Then
                Sheet.Activate
               Set wsN = ActiveSheet
                Exit For
            End If
        Next
    Hope that helps.
    Tony

    AbUsing Excel 2002 to 2013
    This line will be updated just as soon as I come up with something, original, witty, profound or interesting. Please don't hold your breath!

    Please remember - we cannot see your workbook nor read your mind.
    Help us to help you, post clear detail of what you have and what you want - from the start.

    A screen shot can save a thousand words!
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker
    If posting VBA code, please use Code Tags - see: here

  3. #3
    Board Regular rjplante's Avatar
    Join Date
    Oct 2008
    Posts
    413
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: worksheet copy

    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?

  4. #4
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: worksheet copy

    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

  5. #5
    Board Regular rjplante's Avatar
    Join Date
    Oct 2008
    Posts
    413
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: worksheet copy

    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

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •