Trouble with Tutorial (Adding Worksheet to Existing Workbook - VBA)

[bmoff28]

Dear Experts,

I have a problem & a question re: this tutorial on mrexcel.com: http://www.mrexcel.com/articles/macro-to-add-daily-report.php. I'm trying to utilize this in a PO system for work. Unfortunately, I keep receiving a Run-time error '9' (subscript out of range) on this specific line:

Sheets(TabName).Copy After:=Workbooks(ControlFile).Sheets(1)

The code is strictly copy/pasted into my VBA editor, except changing the names inbetween each of the quotes ("") for specific sheet/cell names. I'm not a programmer, but I've read through the code and logically can't deduce why it shouldn't work for me.

My other related question is that I would like to have the new worksheet sent to the end of the active workbook, so would substituting "Sheets(Sheets.Count)" at the end of the above line of code work to send it to the end of the workbook?

Thanks for your help ahead of time.

 

[njimack]

Unless ControlFile is a variable that you have assigned a workbook name to, it should be in quotes

 

[VoG]

You would get that error if the sheet named in the variable TabName did not exist (maybe it has leading or trailing spaces for example).

To copy after the last sheet

Sheets(TabName).Copy After:=Workbooks(ControlFile).Sheets(Sheets.Count)

 

[bmoff28]

I haven't renamed any Variables, so ControlFile still is ActiveWorkbook.Name and TabName should correspond to the cell as directed.

In the process, the error pops up after it opens the worksheet to be copied and the worksheet is renamed correctly, but it won't transfer the worksheet to the open workbook. I thought maybe it wasn't correctly designating the Workbook as the Active Workbook, but I don't know why it wouldn't. The code seems correct.

Any other ideas?

 

[VoG]

Have you checked that the value that you are reading from the cell doesn't contain spaces and that the actual sheet name doesn't contain spaces?

 

[bmoff28]

Yes and yes.

 

[VoG]

Well, the only reason for that error is if either or both sheet TabName and workbook ControlFile do not exist.

Can you post the exact code that you are using.

 

[bmoff28]

Here is my code specifically:

Sub Rename_Tab()
    ' This macro will put the specified number as the new tab
    Sheets("Summary").Select
    Range("S5").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues
    Application.CutCopyMode = False
    Selection.Columns.AutoFit
    Range("S8").Value = ""
End Sub

Sub Add_Blank()
    ' This macro should import the existing spreadsheet
    Sheets("Summary").Select
    PathName = Range("S3").Value
    Filename = Range("S4").Value
    TabName = Range("S5").Value
    ControlFile = ActiveWorkbook.Name
    Workbooks.Open Filename:=PathName & Filename
    ActiveSheet.Name = TabName
    Sheets(TabName).Copy After:=Workbooks(ControlFile).Sheets(Sheets.Count)
    Windows(Filename).Activate
    ActiveWorkbook.Close SaveChanges:=False
    Windows(ControlFile).Activate
    Sheets("Summary").Select
    Range("S8").Select
End Sub

In my case, "Summary" is what the tutorial refers to as "Menu". And the cells are in columns R/S, not columns C/D. And I included the change that you mentioned earlier VoG. Other than that....?

Edit: I did remove a couple lines at the bottom, because I don't need it to tell me the action completed. Irrelevant to the problem, I believe.

 

[bmoff28]

Well, was fooling around some more and found some success. Changed:

Sheets(TabName).Copy After:=Workbooks(ControlFile).Sheets(1)</pre>

to

Workbooks("workbookname.xls").Sheets(Sheets.Count).Copy After:=Workbooks(ControlFile).Sheets(Sheets.Count)

Seems to have solved the problem. Although, now it doesn't send the new worksheet to the end for some reason.

 

[njimack]

You've got a lot of unecessary selecting going on there. The below code

Sheets("Summary").Select
    Range("S5").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues
    Application.CutCopyMode = False
    Selection.Columns.AutoFit
    Range("S8").Value = ""

could/should be rewritten as

With Sheets("Summary")
.Range("S5").Formula = .Range("S5").Value
.Columns("S:S").Autofit
.Range("S8").ClearContents
End With