Solved

Problem copying Excel worksheets with VBA

Posted on 2011-03-20
20
288 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
 
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now