Run Time Error 9 on Copy and Paste Sheets to Wrkbk

gottimd

Well-known Member
Joined
Jul 29, 2002
Messages
501
Windows("Entity Pack Template.xls").Activate
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets(Array("Companies", "TITLEPAGE", "Data", "Assets", "Liabilities_Equity", _
"Income_Statement", "Intercompany", "Inventory", "PPE-MONTH", "Intangibles", _
"Headcount", "Debt", "Debt Worksheet", "Bonus", "Statistics", "Check")).Select
Sheets("Companies").Activate
Sheets(Array("Companies", "TITLEPAGE", "Data", "Assets", "Liabilities_Equity", _
"Income_Statement", "Intercompany", "Inventory", "PPE-MONTH", "Intangibles", _
"Headcount", "Debt", "Debt Worksheet", "Bonus", "Statistics", "Check")).Copy Before _
:=Workbooks(Fullnme).Sheets(1)

Sheets("Sheet1").Select
ActiveWindow.SelectedSheets.Delete
Sheets("Sheet2").Select
ActiveWindow.SelectedSheets.Delete
Sheets("Sheet3").Select
ActiveWindow.SelectedSheets.Delete
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst

Why is this giving me a runtime error "script out of range"?
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

gottimd

Well-known Member
Joined
Jul 29, 2002
Messages
501
Basically I want to copy all sheets in workbook "Entity Pack Template", to a new workbook, and it won't let me do it. Not sure if my array is wrong.
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
Hi, I think its because there is no workbook called Fullnme. So you have to have Fullnme.xls in quotes or put the name of the book.

Sheets(Array("Companies", "TITLEPAGE", "Data", "Assets", "Liabilities_Equity", _
"Income_Statement", "Intercompany", "Inventory", "PPE-MONTH", "Intangibles", _
"Headcount", "Debt", "Debt Worksheet", "Bonus", "Statistics", "Check")).Copy Before _
:=Workbooks("Fullnme.xls").Sheets(1)

Alternatively why not just make a copy of the whole workbook?
 

kkknie

Well-known Member
Joined
Apr 29, 2002
Messages
677
Couldn't you just save it as a different name (in code of course)? If you do this manually and record it, you'll have the code. You could also record manually selecting all of the sheets and copying to a new workbook.

K
 

gottimd

Well-known Member
Joined
Jul 29, 2002
Messages
501
This is my code. Whenever I post it, no one responds. I have the workbook.add command in there, but it added workbook needs to have all of the templates sheets in it.

Code:
Sub TESTFORINDIVIDUAL()
'
'

    Dim wbx As Workbook
    Dim wby As Workbook
    Dim Month As String
    Dim Entity
    Dim underscore
    Dim Hypname
    Dim Fullnme
    Dim Mnth
    Dim Year
    Dim Consolidation
    Dim Response As Integer
    Dim Snd
    Dim Flder
    Dim dte
    Dim rsp
    Dim ws As Worksheet
    Dim I As Integer
    Dim x As Long
    Dim r As Long
    Dim sh As Worksheet
    r = 1

     
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    Set wbx = ActiveWorkbook
    
    'Change name of drop down to suit
    
        With Worksheets("Data").DropDowns("Drop Down 17")
        For x = 1 To .ListCount
            .ListIndex = x
   
            'calculates and refreshes open workbook
    
   
             Sheets("TITLEPAGE").Activate
             ActiveSheet.Calculate
             Sheets("Data").Activate
             ActiveSheet.Calculate
             Sheets("Companies").Select
             ActiveWindow.ScrollWorkbookTabs Position:=xlLast
             Sheets(Array("Companies", "TITLEPAGE", "Data", "Assets", "Liabilities_Equity", _
                "Income_Statement", "Intercompany", "Inventory", "PPE-MONTH", "Intangibles", _
                "Headcount", "Debt", "Debt Worksheet", "Bonus", "Statistics", "Check")).Select
             Calculate
    
             'sets open workbook hyperion name and saves into send file
    
             Mnth = Sheets("TITLEPAGE").Range("a52")
             Year = Sheets("TITLEPAGE").Range("a53")
             Consolidation = Sheets("TITLEPAGE").Range("a54")
             Fldr = Sheets("TITLEPAGE").Range("a58")
             Month = Mnth & " " & Year & " " & Consolidation
             Entity = Sheets("TITLEPAGE").Range("A55")
             underscore = Sheets("TITLEPAGE").Range("A56")
             Hypname = Sheets("TITLEPAGE").Range("A57")
             Snd = Sheets("TITLEPAGE").Range("a58")
             dte = Sheets("TITLEPAGE").Range("a59")
             
             Fullnme = Entity & underscore & Hypname & Snd & underscore & dte
           
             Workbooks.Add.Saveas Filename:="E:\Groups\Hyperion\Acterna\Corp\FY2004\" & Month & "\Packs to load\Send\" & Fullnme & ".xls"
             
             Windows("Entity Pack Template.xls").Activate
             ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
             Sheets(Array("Companies", "TITLEPAGE", "Data", "Assets", "Liabilities_Equity", _
                 "Income_Statement", "Intercompany", "Inventory", "PPE-MONTH", "Intangibles", _
                 "Headcount", "Debt", "Debt Worksheet", "Bonus", "Statistics", "Check")).Select
             Sheets("Companies").Activate
             Sheets(Array("Companies", "TITLEPAGE", "Data", "Assets", "Liabilities_Equity", _
                 "Income_Statement", "Intercompany", "Inventory", "PPE-MONTH", "Intangibles", _
                 "Headcount", "Debt", "Debt Worksheet", "Bonus", "Statistics", "Check")).Copy Before _
                 :=Workbooks("Fullnme.xls").Sheets(1)
             Sheets("Sheet1").Select
             ActiveWindow.SelectedSheets.Delete
             Sheets("Sheet2").Select
             ActiveWindow.SelectedSheets.Delete
             Sheets("Sheet3").Select
             ActiveWindow.SelectedSheets.Delete
             ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
                           
         
            'Formats and copy and paste values so enduser can see values instead of Hyperion links
        
             Sheets("TITLEPAGE").Visible = False
             Sheets("Assets").Select
             Range("D13:D124").Select
             Selection.Copy
             Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                 :=False, Transpose:=False
             Sheets("Liabilities_Equity").Select
             Range("D12:D93").Select
             Application.CutCopyMode = False
             Selection.Copy
             Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                 :=False, Transpose:=False
             Sheets("Income_Statement").Select
             Range("D12:D140").Select
             Application.CutCopyMode = False
             Selection.Copy
             Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                 :=False, Transpose:=False
             Sheets("Statistics").Select
             Range("D17:D20").Select
             Application.CutCopyMode = False
             Selection.Copy
             Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                 :=False, Transpose:=False
             Range("D22:D27").Select
             Application.CutCopyMode = False
             Selection.Copy
             Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                 :=False, Transpose:=False
             Range("D33").Select
             Range("D33:D35").Select
             Application.CutCopyMode = False
             Selection.Copy
             Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                 :=False, Transpose:=False
             Range("D39:D41").Select
             Application.CutCopyMode = False
             Selection.Copy
             Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                 :=False, Transpose:=False
             ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
             Sheets("Data").Select
       
             'Protects all sheets so user cannot alter
    
              rsp = Sheets("Data").Range("B112").Value
    
              For I = 1 To Worksheets.Count
                      Application.ScreenUpdating = False
                      Worksheets(I).Protect rsp
                      Sheets("Data").Select
                      Sheets("data").unprotect rsp
                      Rows("111:117").Select
                      Selection.FormulaHidden = True
                      Selection.EntireRow.Hidden = True
                      Selection.Locked = True
                      Sheets("data").Protect rsp
                
              Next
       
              'formats inventory page so end user can enter data
    
              Sheets("Inventory").Select
              Sheets("Inventory").unprotect rsp
              Sheets("Inventory").Select
              ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
                  False, AllowUsingPivotTables:=True
              Sheets("Data").Select
              Range("B2").Select
        
              'Saves active workbook to be sent and opens up template to start for new entity and closes finished template
        
              ActiveWorkbook.Save
              ActiveWorkbook.Close
              
              Windows("Entity Pack Template.xls").Activate

            r = r + 1
        Next x
    
       Application.ScreenUpdating = True
       Application.DisplayAlerts = True
    
    End With
    
End Sub

and now no one will respond.
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
Hi, if you want your current workbook to remain open and just make a copy then use SaveCopyAs

Code:
ActiveWorkbook.SaveCopyAs "C:\TEMP\XXXX.XLS"

If you want to close the existing workbook and open the new book then use SaveAs

Code:
ActiveWorkbook.SaveAs "C:\TEMP\XXXX.XLS"

hth
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,322
Messages
5,769,448
Members
425,546
Latest member
DisMissive

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
Top