worksheet copy

rjplante

Active Member
Joined
Oct 31, 2008
Messages
473
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!
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,055
Office Version
2013
Platform
Windows
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
Joined
Oct 31, 2008
Messages
473
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
Joined
Apr 28, 2010
Messages
167
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
Joined
Oct 31, 2008
Messages
473
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
 

Watch MrExcel Video

Forum statistics

Threads
1,101,889
Messages
5,483,522
Members
407,397
Latest member
HerbA

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top