worksheet copy

rjplante

Well-known Member
Joined
Oct 31, 2008
Messages
558
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top