Solved

Problem copying Excel worksheets with VBA

Posted on 2011-03-20
20
289 Views
Last Modified: 2012-05-11
I have a function here whcih basically dusplicates a template worksheet (Blank) and copies it X amount fo times to thwe current workbook. Hoeever I am getting these following error

Run-time error '1004':

Cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by Visual Basic.

I don't really understand as I am trying to set the name dynamically with sh.Name = strname which is: OL 1, OL 2, OL 3 etc.

Can anyone see where I am going wrong?
Sub GenSheets()

Dim rng    As Range
    Dim cl     As Range
    Dim strname As String
    
    With Sheets("Quotation")
        Set rng = .Range(.Cells(6, 1), .Cells(6, 1).End(xlDown))
        For Each cl In rng
        
            strname = "OL " & cl.Value
            MsgBox (strname)
            'Blank is the name of my template worksheet
            Sheets("Blank").Copy After:=Sheets(Sheets.Count)
            Set sh = Sheets(Sheets.Count)
            sh.Name = strname
            sh.Visible = True 'xlHidden
            sh.Cells(3, 2) = "=Quotation!$F$" & cl.Value + 5
            sh.Cells(3, 3) = "=Quotation!$B$" & cl.Value + 5
            sh.Cells(3, 4) = "=Quotation!$C$" & cl.Value + 5
            sh.Cells(3, 5) = "=Quotation!$D$" & cl.Value + 5
            sh.Cells(3, 7) = "=Quotation!$E$" & cl.Value + 5
            Set q = Sheets("Quotation")
            q.Cells(cl.Value + 5, 35) = "='" & strname & "'!$J$30"
        Next cl
    End With
    
    End Sub

Open in new window

0
Comment
Question by:wellso
  • 11
  • 4
  • 4
  • +1
20 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35176507
In this case I trust Excel and you are very likely doing what it says you are doing. Could you have some hidden sheets that have that name?

Kevin
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35176514
You need to make sure that cells A6 A7 A8.... contains the values 1, 2, 3....
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35176526
Another possibility is that only A6 contains a value 1 and the further cells are blank
0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35176528
These cells are on sheet "Quotation"
0
 
LVL 2

Author Comment

by:wellso
ID: 35176564
The cells are numeric, 1,2,3,4 etc until a blank cell is encountered

I seem to have had it going once before, but now I can't get it to work for the life of me,

when i do MsgBox (strname) the new names look fine (OL1, OL2 etc) but it wont rename the sheets to that
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35176632
In that case Kevin's proposal seems appropriate.

Go to VBA and on the Project Explorer pane on the left edge of VBA screen. (if you cannot see it then turn it on from the view menu) see if you can see a sheet with the name OL1 which is hidden

Saqib
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35176641
If you still have a problem post your file here. You can delete values from the sheets if do not want to public the contents.

Saqib
0
 
LVL 2

Author Comment

by:wellso
ID: 35176676
it still breaks on  sh.Name = strname

there is no existing sheets with that name, i looked in the explorer view. it just inserts one sheets as Blank(2)
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35176700
Can you upload the file?
0
 
LVL 2

Author Comment

by:wellso
ID: 35176706
here is my file
test.xls
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35176783
There is some problem with one of the sheets. I am unable to single it out. Try to select all the sheets and copy them to a new workbook and do the same with the code modules. It should work with the new file.

Saqib
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 35176807
Ok found it. Delete the Module (not sheet) "Auto" and the macro will run.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35176820
Don't ask me why because I do not have the faintest idea why excel is considering Auto as a sheet instead of a module and because of this the sheet numbering system gets thrown off.

Saqib
0
 
LVL 2

Author Closing Comment

by:wellso
ID: 35176829
thankyou ever so much for all your help, you are an absolute legend
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35176839
Kevin, If you read this please take a look at the file and comment. Although I have managed to solve the problem I am at a loss as to the logic.

wellso,
That does flatter me but I really do not deserve such high praise. Thanks for the points.

Saqib
0
 
LVL 33

Expert Comment

by:Norie
ID: 35176880
There is something weird going on here.

For some reason a module is being created when you run the GenSheets sub.

At first I thought the problem might be that OL1 is a valid cell reference in later versions of Excel but I think that can
be ruled out since you are adding a space so it's 'OL 1'.

I only got the error on the first run and made some changes and can't seem to undo them to recreate the problem.

I'll try re-opening the file without saving.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35176898
imnorie, The module is not created by the sub it is already there in the test file. But in VBA it is being treated as a worksheet.
0
 
LVL 33

Expert Comment

by:Norie
ID: 35176916
Right, I think I know why it might be happening - partially anyway

It's the module called Auto causing the problem.

Somehow it's being treated as a 'Sheet, you can see this if you type ?Sheets(22).Name & "-" & Typename(Sheets(22)) in the Immediate window.

It returns Auto-Module.

So when you hit this line

Set sh = Sheets(Sheets.Count)

It can be fixed by using Worksheet/worksheets where appropriate.

Sub GenSheets()
Dim sh As Worksheet
Dim rng As Range
Dim cl As Range
Dim strname As String

    With Worksheets("Quotation")
    
        Set rng = .Range(.Cells(6, 1), .Cells(6, 1).End(xlDown))
        
        For Each cl In rng

            strname = "OL " & cl.Value
            
            'Blank is the name of my template worksheet
            
            Worksheets("Blank").Copy After:=Worksheets(Worksheets.Count)
            
            Set sh = Worksheets(Worksheets.Count)
            sh.Name = strname
            
            sh.Visible = True    'xlHidden
            
            sh.Cells(3, 2) = "=Quotation!$F$" & cl.Value + 5
            sh.Cells(3, 3) = "=Quotation!$B$" & cl.Value + 5
            sh.Cells(3, 4) = "=Quotation!$C$" & cl.Value + 5
            sh.Cells(3, 5) = "=Quotation!$D$" & cl.Value + 5
            sh.Cells(3, 7) = "=Quotation!$E$" & cl.Value + 5
            
            Set q = Sheets("Quotation")
            q.Cells(cl.Value + 5, 35) = "='" & strname & "'!$J$30"
        Next cl
    End With

End Sub

Open in new window

0
 
LVL 33

Expert Comment

by:Norie
ID: 35176934
ssagibh

I never said the module was being created by the code, it is however being referenced by the code, see my post above.

There are 21 worksheets in the workbook and 22 other sheets.

That extra 'sheet' is apparently the module named Auto.

Here's a full listing of all the sheets in the workbookk, before the code is run.

Doc_Info - Worksheet
Quotation - Worksheet
Packing - Worksheet
GI Costs Lugged A - Worksheet
GI Costs BR - Worksheet
Info - Worksheet
B Mc - Worksheet
Trim - Worksheet
Blank - Worksheet
DBase - Worksheet
DBase2 - Worksheet
GSC1 - Worksheet
Nov 2004 - Worksheet
Feb 2004 - Worksheet
Previous Weights - Worksheet
GSC - Worksheet
Wghts - Worksheet
Drill - Worksheet
Pies - Worksheet
Oct 2005 - Worksheet
Foundry Oct 2005 - Worksheet
Auto - Module

I'm not 100% sure but I've a feeling that in earlier versions of Excel a module called Auto had some 'special purpose.
0
 
LVL 33

Expert Comment

by:Norie
ID: 35176940
Oops, my bad I did think it was being created.

Sorry about that.
0

Featured Post

ScreenConnect 6.0 Free Trial

Want empowering updates? You're in the right place! Discover new features in ScreenConnect 6.0, based on partner feedback, to keep you business operating smoothly and optimally (the way it should be). Explore all of the extras and enhancements for yourself!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question