Run Time Error 9 on Copy and Paste Sheets to Wrkbk

G

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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Run-time error 9, Subscript out of range - Copying sheet from closed workbook to active workbook as new sheet
Replies
9
Views
290
dvward
D
M
  • Question
Run Time Error 9 on Deleting sheet
Replies
5
Views
744
mgCulver
M
S
  • Question
Run-time error 1004 Copy and Paste
Replies
0
Views
598
Sherifa
S
E
  • Question
Run-Time error 9
Replies
1
Views
713
mole999
mole999
D
  • Question
coding for copying,pasting,eliminating "0" rows,formatting
Replies
1
Views
841
dstepan
D

Forum statistics

Threads
1,214,585
Messages
6,120,388
Members
448,957
Latest member
Hat4Life

Share this page

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
Back
Top