Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

Problem copying Excel worksheets with VBA

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
wellso
Asked:
wellso
  • 11
  • 4
  • 4
  • +1
1 Solution
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
Saqib Husain, SyedEngineerCommented:
You need to make sure that cells A6 A7 A8.... contains the values 1, 2, 3....
0
 
Saqib Husain, SyedEngineerCommented:
Another possibility is that only A6 contains a value 1 and the further cells are blank
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Saqib Husain, SyedEngineerCommented:
These cells are on sheet "Quotation"
0
 
wellsoAuthor Commented:
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
 
Saqib Husain, SyedEngineerCommented:
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
 
Saqib Husain, SyedEngineerCommented:
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
 
wellsoAuthor Commented:
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
 
Saqib Husain, SyedEngineerCommented:
Can you upload the file?
0
 
wellsoAuthor Commented:
here is my file
test.xls
0
 
Saqib Husain, SyedEngineerCommented:
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
 
Saqib Husain, SyedEngineerCommented:
Ok found it. Delete the Module (not sheet) "Auto" and the macro will run.
0
 
Saqib Husain, SyedEngineerCommented:
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
 
wellsoAuthor Commented:
thankyou ever so much for all your help, you are an absolute legend
0
 
Saqib Husain, SyedEngineerCommented:
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
 
NorieCommented:
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
 
Saqib Husain, SyedEngineerCommented:
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
 
NorieCommented:
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
 
NorieCommented:
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
 
NorieCommented:
Oops, my bad I did think it was being created.

Sorry about that.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 11
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now